locked
entity relationship 3rd normal form RRS feed

Answers

  • It is important that you indicate NATURAL KEYs as UNIQUE.

    Here is my try:

    CREATE TABLE organization_types
     (
     organization_type_id int NOT NULL AUTO_INCREMENT,
     organization_type_name varchar (50) NOT NULL UNIQUE,
     PRIMARY KEY (organization_type_id) 
     );
     ****************************
     CREATE TABLE organizations
     (
     organization_id int NOT NULL AUTO_INCREMENT,
     organization_name varchar(50) NOT NULL UNIQUE,
     date_of_establishment date,
     organization_rating varchar(20),
     location_id int NOT NULL,
     organization_type_id int NOT NULL,
     PRIMARY KEY (organization_id)
     );
     **********************
     CREATE TABLE countries
     (
     country_name varchar (60) NOT NULL UNIQUE,
     country_code varchar (10) UNIQUE,
     country_currency varchar (10),
     country_language varchar (30),
     USD_exchange_rate double,
     safety_rating int (10),
     PRIMARY KEY (country_name)
     );
     ************************
     CREATE TABLE location_types
     (
     location_type_code varchar (10) NOT NULL,
     location_type_description varchar (20) UNIQUE,
     PRIMARY KEY (location_type_code)
     );
     ************************
     CREATE TABLE locations
     (
     location_id int NOT NULL AUTO_INCREMENT,
     place_number int NOT NULL,
     street_name varchar (50),
     city varchar (50),
     state varchar (50),
     postcode int,
     location_type_code varchar (10) NOT NULL,
     country_name varchar (60) NOT NULL
     PRIMARY KEY (location_id)
     );
     ************************
     CREATE TABLE product_services_types
     (
     product_svc_type_id int NOT NULL AUTO_INCREMENT,
     product_svc_type_name varchar (50) UNIQUE,
     product_svs_type_description varchar (50),
     PRIMARY KEY (product_svc_type_id)
     );
     ************************
     CREATE TABLE product_services
     (
     product_services_id int NOT NULL AUTO_INCREMENT,
     product_services_name varchar (50) UNIQUE,
     product_services_quantity int,
     product_services_price varchar (50),
     organization_id int NOT NULL,
     product_svc_type_id int NOT NULL,
     PRIMARY KEY (product_services_id)
     );
     ************************
     CREATE TABLE employee_records
     (
     employee_id int NOT NULL AUTO_INCREMENT,
     employee_name varchar (50),
     ssn char(9) not null UNIQUE,
     employee_age int (3),
     employee_years_of_service int (3),
     employee_years_in_jail int (3),
     organization_id int NOT NULL,
     PRIMARY KEY (employee_id)
     );
     ****************************
     CREATE TABLE skills
     (
     special_skill_id int NOT NULL AUTO_INCREMENT,
     special_skill_description varchar (30) UNIQUE,
     special_skill_level varchar (10),
     employee_id int NOT NULL,
     PRIMARY KEY (special_skill_id)
     );
     *************************
     CREATE TABLE shipments
     (
     shipment_id int NOT NULL AUTO_INCREMENT,
     shipment_cargo_details varchar (50) UNIQUE,
     organization_id int NOT NULL,
     PRIMARY KEY (shipment_id)
     );
     ***********************
     CREATE TABLE payments
     (
     payment_id int NOT NULL AUTO_INCREMENT,
     payment_date date,
     payment_amount varchar (50),
     payment_type_id int NOT NULL,
     organization_id int NOT NULL,
     PRIMARY KEY (payment_id)
     );
     *********************
     CREATE TABLE payment_types
     (
     payment_type_id int NOT NULL AUTO_INCREMENT,
     payment_type_name varchar (50) UNIQUE,
     payment_type_amount varchar (50),
     PRIMARY KEY (payment_type_id)
     );
     ***********************
     CREATE TABLE shipment_products_services
     (
     shipment_date DATE,
     shipment_id int NOT NULL ,
     product_services_id int NOT NULL,
     payment_id int NOT NULL, 
     PRIMARY KEY (shipment_date)
     );
     ************************
     CREATE TABLE shipment_locations
     (
     shipment_location_id int NOT NULL AUTO_INCREMENT,
     shipment_details varchar(50) UNIQUE,
     shipment_completed DATE,
     shipment_id int NOT NULL,
     location_id int NOT NULL,
     shipment_date DATE,
     PRIMARY KEY (shipment_location_id)
     );



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by tracycai Monday, May 19, 2014 9:50 AM
    Sunday, May 11, 2014 12:04 AM

All replies

  • Hi Andries,

    After a brief glance it looks like you are not too far off 3rd normal form.

    The main difficultly I have is that you have not given your tables / entities meaningful names. You really do need to do this as a pre-requisite. So; -

    1) please give your entities / proposed tables meaningful names.

    2) update your question text accordingly.

    3) attach your proposed 3rd normal form entity relationship diagram.

    We should then be better placed to help you.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Saturday, May 10, 2014 8:57 PM
  • Can you post them in CREATE TABLE format? Thanks.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Saturday, May 10, 2014 10:02 PM
  • CREATE TABLE organization_types
    (
    organization_type_id int NOT NULL AUTO_INCREMENT,
    organization_type_name varchar (50),
    PRIMARY KEY (organization_type_id)
    );
    ****************************
    CREATE TABLE organizations
    (
    organization_id int NOT NULL AUTO_INCREMENT,
    organization_name varchar(50),
    date_of_establishment date,
    organization_rating varchar(20),
    location_id int NOT NULL,
    organization_type_id int NOT NULL,
    PRIMARY KEY (organization_id)
    );
    **********************
    CREATE TABLE countries
    (
    country_name varchar (60) NOT NULL,
    country_code varchar (10),
    country_currency varchar (10),
    country_language varchar (30),
    USD_exchange_rate double,
    safety_rating int (10),
    PRIMARY KEY (country_name)
    );
    ************************
    CREATE TABLE location_types
    (
    location_type_code varchar (10) NOT NULL,
    location_type_description varchar (20),
    PRIMARY KEY (location_type_code)
    );
    ************************
    CREATE TABLE locations
    (
    location_id int NOT NULL AUTO_INCREMENT,
    place_number int NOT NULL,
    street_name varchar (50),
    city varchar (50),
    state varchar (50),
    postcode int,
    location_type_code varchar (10) NOT NULL,
    country_name varchar (60) NOT NULL
    PRIMARY KEY (location_id)
    );
    ************************
    CREATE TABLE product_services_types
    (
    product_svc_type_id int NOT NULL AUTO_INCREMENT,
    product_svc_type_name varchar (50),
    product_svs_type_description varchar (50),
    PRIMARY KEY (product_svc_type_id)
    );
    ************************
    CREATE TABLE product_services
    (
    product_services_id int NOT NULL AUTO_INCREMENT,
    product_services_name varchar (50),
    product_services_quantity int,
    product_services_price varchar (50),
    organization_id int NOT NULL,
    product_svc_type_id int NOT NULL,
    PRIMARY KEY (product_services_id)
    );
    ************************
    CREATE TABLE employee_records
    (
    employee_id int NOT NULL AUTO_INCREMENT,
    employee_name varchar (50),
    employee_age int (3),
    employee_years_of_service int (3),
    employee_years_in_jail int (3),
    organization_id int NOT NULL,
    PRIMARY KEY (employee_id)
    );
    ****************************
    CREATE TABLE skills
    (
    special_skill_id int NOT NULL AUTO_INCREMENT,
    special_skill_description varchar (30),
    special_skill_level varchar (10),
    employee_id int NOT NULL,
    PRIMARY KEY (special_skill_id)
    );
    *************************
    CREATE TABLE shipments
    (
    shipment_id int NOT NULL AUTO_INCREMENT,
    shipment_cargo_details varchar (50),
    organization_id int NOT NULL,
    PRIMARY KEY (shipment_id)
    );
    ***********************
    CREATE TABLE payments
    (
    payment_id int NOT NULL AUTO_INCREMENT,
    payment_date date,
    payment_amount varchar (50),
    payment_type_id int NOT NULL,
    organization_id int NOT NULL,
    PRIMARY KEY (payment_id)
    );
    *********************
    CREATE TABLE payment_types
    (
    payment_type_id int NOT NULL AUTO_INCREMENT,
    payment_type_name varchar (50),
    payment_type_amount varchar (50),
    PRIMARY KEY (payment_type_id)
    );
    ***********************
    CREATE TABLE shipment_products_services
    (
    shipment_date DATE,
    shipment_id int NOT NULL ,
    product_services_id int NOT NULL,
    payment_id int NOT NULL,
    PRIMARY KEY (shipment_date)
    );
    ************************
    CREATE TABLE shipment_locations
    (
    shipment_location_id int NOT NULL AUTO_INCREMENT,
    shipment_details varchar(50),
    shipment_completed DATE,
    shipment_id int NOT NULL,
    location_id int NOT NULL,
    shipment_date DATE,
    PRIMARY KEY (shipment_location_id)
    );
    ************************

    ********************

    ALTER TABLE locations
    ADD FOREIGN KEY (location_type_code)
    REFERENCES location_types(location_type_code),
    ADD FOREIGN KEY (country_name)
    REFERENCES countries(country_name)

    ********************

    ALTER TABLE shipment_locations
    ADD FOREIGN KEY (shipment_id)
    REFERENCES shipments(shipment_id),
    ADD FOREIGN KEY (location_id)
    REFERENCES locations(location_id),
    ADD FOREIGN KEY (shipment_date)
    REFERENCES shipment_products_services(shipment_date)

    ********************

    ALTER TABLE shipment_products_services
    ADD FOREIGN KEY (shipment_id)
    REFERENCES shipments(shipment_id),
    ADD FOREIGN KEY (product_services_id)
    REFERENCES product_services(product_services_id),
    ADD FOREIGN KEY (payment_id)
    REFERENCES payments(payment_id)

    ********************

    ALTER TABLE shipments
    ADD FOREIGN KEY (organization_id)
    REFERENCES organizations(organization_id)

    ********************

    ALTER TABLE payments
    ADD FOREIGN KEY (organization_id)
    REFERENCES organizations(organization_id),
    ADD FOREIGN KEY (payment_type_id)
    REFERENCES payment_types(payment_type_id)

    ********************

    ALTER TABLE product_services
    ADD FOREIGN KEY (organization_id)
    REFERENCES organizations(organization_id),
    ADD FOREIGN KEY (product_svc_type_id)
    REFERENCES product_services_types(product_svc_type_id)

    ********************

    ALTER TABLE skills
    ADD FOREIGN KEY (employee_id)
    REFERENCES employee_records(employee_id)

    ********************

    ALTER TABLE employee_records
    ADD FOREIGN KEY (location_id)
    REFERENCES locations(location_id),
    ADD FOREIGN KEY (organization_id)
    REFERENCES organizations(organization_id)

    ********************

    ALTER TABLE organizations
    ADD FOREIGN KEY (organization_type_id)
    REFERENCES organization_types(organization_type_id),
    ADD FOREIGN KEY (location_id)
    REFERENCES locations(location_id)

    ********************

    this is how i see it working after now to be my 10th attempt
    dont know if im over thinking it too much or not

    Saturday, May 10, 2014 10:22 PM
  • It is important that you indicate NATURAL KEYs as UNIQUE.

    Here is my try:

    CREATE TABLE organization_types
     (
     organization_type_id int NOT NULL AUTO_INCREMENT,
     organization_type_name varchar (50) NOT NULL UNIQUE,
     PRIMARY KEY (organization_type_id) 
     );
     ****************************
     CREATE TABLE organizations
     (
     organization_id int NOT NULL AUTO_INCREMENT,
     organization_name varchar(50) NOT NULL UNIQUE,
     date_of_establishment date,
     organization_rating varchar(20),
     location_id int NOT NULL,
     organization_type_id int NOT NULL,
     PRIMARY KEY (organization_id)
     );
     **********************
     CREATE TABLE countries
     (
     country_name varchar (60) NOT NULL UNIQUE,
     country_code varchar (10) UNIQUE,
     country_currency varchar (10),
     country_language varchar (30),
     USD_exchange_rate double,
     safety_rating int (10),
     PRIMARY KEY (country_name)
     );
     ************************
     CREATE TABLE location_types
     (
     location_type_code varchar (10) NOT NULL,
     location_type_description varchar (20) UNIQUE,
     PRIMARY KEY (location_type_code)
     );
     ************************
     CREATE TABLE locations
     (
     location_id int NOT NULL AUTO_INCREMENT,
     place_number int NOT NULL,
     street_name varchar (50),
     city varchar (50),
     state varchar (50),
     postcode int,
     location_type_code varchar (10) NOT NULL,
     country_name varchar (60) NOT NULL
     PRIMARY KEY (location_id)
     );
     ************************
     CREATE TABLE product_services_types
     (
     product_svc_type_id int NOT NULL AUTO_INCREMENT,
     product_svc_type_name varchar (50) UNIQUE,
     product_svs_type_description varchar (50),
     PRIMARY KEY (product_svc_type_id)
     );
     ************************
     CREATE TABLE product_services
     (
     product_services_id int NOT NULL AUTO_INCREMENT,
     product_services_name varchar (50) UNIQUE,
     product_services_quantity int,
     product_services_price varchar (50),
     organization_id int NOT NULL,
     product_svc_type_id int NOT NULL,
     PRIMARY KEY (product_services_id)
     );
     ************************
     CREATE TABLE employee_records
     (
     employee_id int NOT NULL AUTO_INCREMENT,
     employee_name varchar (50),
     ssn char(9) not null UNIQUE,
     employee_age int (3),
     employee_years_of_service int (3),
     employee_years_in_jail int (3),
     organization_id int NOT NULL,
     PRIMARY KEY (employee_id)
     );
     ****************************
     CREATE TABLE skills
     (
     special_skill_id int NOT NULL AUTO_INCREMENT,
     special_skill_description varchar (30) UNIQUE,
     special_skill_level varchar (10),
     employee_id int NOT NULL,
     PRIMARY KEY (special_skill_id)
     );
     *************************
     CREATE TABLE shipments
     (
     shipment_id int NOT NULL AUTO_INCREMENT,
     shipment_cargo_details varchar (50) UNIQUE,
     organization_id int NOT NULL,
     PRIMARY KEY (shipment_id)
     );
     ***********************
     CREATE TABLE payments
     (
     payment_id int NOT NULL AUTO_INCREMENT,
     payment_date date,
     payment_amount varchar (50),
     payment_type_id int NOT NULL,
     organization_id int NOT NULL,
     PRIMARY KEY (payment_id)
     );
     *********************
     CREATE TABLE payment_types
     (
     payment_type_id int NOT NULL AUTO_INCREMENT,
     payment_type_name varchar (50) UNIQUE,
     payment_type_amount varchar (50),
     PRIMARY KEY (payment_type_id)
     );
     ***********************
     CREATE TABLE shipment_products_services
     (
     shipment_date DATE,
     shipment_id int NOT NULL ,
     product_services_id int NOT NULL,
     payment_id int NOT NULL, 
     PRIMARY KEY (shipment_date)
     );
     ************************
     CREATE TABLE shipment_locations
     (
     shipment_location_id int NOT NULL AUTO_INCREMENT,
     shipment_details varchar(50) UNIQUE,
     shipment_completed DATE,
     shipment_id int NOT NULL,
     location_id int NOT NULL,
     shipment_date DATE,
     PRIMARY KEY (shipment_location_id)
     );



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by tracycai Monday, May 19, 2014 9:50 AM
    Sunday, May 11, 2014 12:04 AM