Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: Database Design Help Needed

    Hi All,

    I need to design IT Asset database. The business rules are follow.

    1. Each employee can have multiple devices such as Laptop, Deskatop, printers Scanners
    2. Each employee is associated with particular department. e.g sales, marketing etc

    I think it is many to many relationship. I've tried to design relationship (refer to attachment) . Your help is appreciated!!. I am in learning phase.

    Many thanks in advance
    mgnep
    Attached Thumbnails Attached Thumbnails db.jpg  

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Learn the differences among entity, attrribute and value

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. We now have to copy things by hand from your pictures, which are full of errors. An employee (entity) has a relationship with a department (entity); a department is not an attribute! Likewise, an asset category is an attribute of an asset, not an entity.

    You have two separate relationships. Can I assume personnel work in one and only one department? Can I assume personnel have zero or many assets assigned to them?

    CREATE TABLE Personnel
    (emp_id CHAR(10) NOT NULL PRIMARY KEY,
    ..);

    CREATE TABLE Department
    (dept_id CHAR(10) NOT NULL PRIMARY KEY,
    ..);

    CREATE TABLE Job_Assignments
    (emp_id CHAR(10) NOT NULL PRIMARY KEY
    REFERENCES Personnel(emp_id)
    ON DELETE CASCADE,
    dept_id CHAR(10) NOT NULL
    REFERENCES Departments (dept_id)
    ON DELETE CASCADE,

    ..);

    CREATE TABLE Assets
    (asset_id CHAR(10) NOT NULL PRIMARY KEY,
    asset_category CHAR(10) NOT NULL
    CHECK (asset_category IN (..)),
    ..);

    CREATE TABLE Asset_Assignments
    (emp_id CHAR(10) NOT NULL
    REFERENCES Personnel(emp_id)
    ON DELETE CASCADE,
    asset_id CHAR(10) NOT NULL
    REFERENCES Departments (asset_id)
    ON DELETE CASCADE,
    PRIMARY KEY (emp_id, asset_id),
    ..);

    Study the use of UNIQUE constraints to enforce cardinality.

  3. #3
    Join Date
    Nov 2010
    Posts
    3
    Hi Celko,

    Thank you very much for your support. I will try to follow standard naming convention . Could you please kindly tell me the purpose of this CHECK (asset_category IN (..). Thank you once again for your support.

  4. #4
    Join Date
    Nov 2010
    Posts
    3
    sorry about the color CHECK (asset_category IN (..)

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mgnep View Post
    sorry about the color CHECK (asset_category IN (..)
    It is a CHECK CONSTRAINT that ensures that only certain values can be present in that column.
    Code:
    CREATE TABLE PEOPLE(
    	id	BIGINT	NOT NULL,
    	...
    	gender	CHAR(1)
    		CONSTRAINT CC_gender CHECK (gender IN ('F', 'M', 'U')), 
    		-- Female, Male, Unknown
    	..
    	braincells	BIGINT	NOT NULL
    		CONSTRAINT CC_braincells CHECK(braincells >= 1),
    	...
    )
    This code instructs the RDBMS to make sure the possible values in the column
    - gender can only be 'F', 'M' and 'U'
    - braincells can only have positive values starting from 1

    I prefer to give names to the constraints. Otherwise the databasder will invent one for you. I find it more informative to get an error about constraint "CC_gender" than about constraint "SQL-201307241235461254".
    (I just did the test on MSSQL 2008R2: it generated the name "CK__PEOPLE__gender__412EB0B6". I could live with that one. It's an old habit I developed on another database system.)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    -to add to Vim's post ..

    The CHECK() predicate has to test to TRUE or UNKNOWN for each row in the table. They can apply to one or more columns in the table. They assure data integrity. They also pass along their predicate to the optimizer, so they can be a real performance boost.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •