Scribd Logo
Upload

Welcome to Scribd!

    • 51 views

    Creating Company Database Schema and Populating With Data: and Reasoning Behind Constraints

    Uploaded by

    SHAIK SHABEER
    The document describes steps to create and populate a company database schema with sample data. It involves: 1. Creating tables to define the schema including EMPLOYEE, DEPARTMENT, PROJECTS, etc. and defining primary keys and foreign key relationships. 2. Temporarily disabling foreign key checks to populate tables and then re-enabling checks. 3. Populating the tables with sample data using INSERT statements, handling data type conversions and default values as needed.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Creating Company Database Schema and Populating With Data: and Reasoning Behind Constraints

    Uploaded by

    SHAIK SHABEER
    51 views6 pages
    The document describes steps to create and populate a company database schema with sample data. It involves: 1. Creating tables to define the schema including EMPLOYEE, DEPARTMENT, PROJECTS, etc. and defining primary keys and foreign key relationships. 2. Temporarily disabling foreign key checks to populate tables and then re-enabling checks. 3. Populating the tables with sample data using INSERT statements, handling data type conversions and default values as needed.

    Original Title

    Company Databases

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes steps to create and populate a company database schema with sample data. It involves: 1. Creating tables to define the schema including EMPLOYEE, DEPARTMENT, PROJECTS, etc. and defining primary keys and foreign key relationships. 2. Temporarily disabling foreign key checks to populate tables and then re-enabling checks. 3. Populating the tables with sample data using INSERT statements, handling data type conversions and default values as needed.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    51 views6 pages

    Creating Company Database Schema and Populating With Data: and Reasoning Behind Constraints

    Uploaded by

    SHAIK SHABEER
    The document describes steps to create and populate a company database schema with sample data. It involves: 1. Creating tables to define the schema including EMPLOYEE, DEPARTMENT, PROJECTS, etc. and defining primary keys and foreign key relationships. 2. Temporarily disabling foreign key checks to populate tables and then re-enabling checks. 3. Populating the tables with sample data using INSERT statements, handling data type conversions and default values as needed.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 6

    Creating Company Database Schema and Populating with Data

    Step 1: This involves creation of the tables. Comments have been added to indicate various data types
    and reasoning behind constraints.

    -- Create EMPLOYEE table


    CREATE TABLE EMPLOYEE
    ( Fname VARCHAR(15) NOT NULL,
    Minit CHAR,
    Lname VARCHAR(15) NOT NULL,
    Ssn CHAR(9) NOT NULL,
    Bdate DATE,
    Address VARCHAR(30),
    Sex CHAR,
    Salary DECIMAL(10,2),
    Super_ssn CHAR(9),
    Dno INT NOT NULL,
    PRIMARY KEY (Ssn)
    );

    -- Create DEPARTMENT table


    CREATE TABLE DEPARTMENT
    ( Dname VARCHAR(15) NOT
    NULL, Dnumber INT NOT NULL,
    Mgr_ssn CHAR(9) NOT NULL,
    Mgr_start_date DATE,
    PRIMARY KEY (Dnumber),
    UNIQUE (Dname),
    FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );

    -- Now that the DEPARTMENT table is defined, add the Foreign Key constraints
    -- to the EMPLOYEE table.
    -- Super_ssn references Ssn

    ALTER TABLE EMPLOYEE


    ADD CONSTRAINT FKCONSTR1
    FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn);

    -- The Dno column is referenced in the Department table


    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT FKCONSTR2
    FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);

    CREATE TABLE DEPT_LOCATIONS (


    Dnumber INT NOT NULL,
    Dlocation VARCHAR(15) NOT
    NULL,
    PRIMARY KEY (Dnumber, Dlocation),
    FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) );

    CREATE TABLE PROJECT


    ( Pname VARCHAR(15) NOT
    NULL, Pnumber INT NOT NULL,
    Plocation VARCHAR(15),
    Dnum INT NOT NULL,
    PRIMARY KEY (Pnumber),
    UNIQUE (Pname),
    FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) );

    CREATE TABLE WORKS_ON


    ( Essn CHAR(9) NOT NULL,
    Pno INT NOT NULL,
    Hours DECIMAL(3,1) NOT
    NULL, PRIMARY KEY (Essn,
    Pno),
    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
    FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber) );

    CREATE TABLE DEPENDENT


    ( Essn CHAR(9) NOT NULL,
    Dependent_name VARCHAR(15) NOT
    NULL, Sex CHAR,
    Bdate DATE,
    Relationship VARCHAR(8),
    PRIMARY KEY (Essn, Dependent_name),
    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );

    Step 2: Temporarily, the Foreign Key constraint between Dno and Dnumber is unchecked to allow
    seeding of the database with the data provided in the assignment. Once the EMPLOYEE table is
    seeded, INSERT statements are executed to populate the remaining tables in the schema. The data
    mostly matches what is provided in the assignment – the date format had to be modified to YYYY-MM-
    DD, and missing values were set as NULL. “USE COMPANYDB1” enables aliasing of the high level
    relation qualifiers.

    //disable
    SET FOREIGN_KEY_CHECKS=0;
    //enable
    SET FOREIGN_KEY_CHECKS=1;

    INSERT INTO COMPANY.EMPLOYEE values


    ('John', 'B', 'Smith', '123456789', '1955-01-09', '731 Fondren, Houston, TX', 'M', 30000,
    '987654321', 5),
    ('Franklin', 'T', 'Wong', '333445555', '1945-12-08', '638 Voss, Houston, TX', 'M', 40000,
    '888665555', 5),
    ('Joyce', 'A', 'English', '453453453', '1962-12-31', '5631 Rice, Houston, TX', 'F',
    25000, '333445555', 5),
    ('Ramesh', 'K', 'Narayan', '666884444', '1952-09-15', 'Fire Oak, Humble, TX', 'M', 38000,
    '333445555', 5),
    ('James', 'E', 'Borg', '888665555', '1927-11-10', 'Stone, Houston, TX', 'M', 55000, NULL,
    1),
    ('Jennifer', 'S', 'Wallace', '987654321', '1931-06-20', 'Berry, Bellaire, TX', 'F',
    43000, '888665555', 4),
    ('Ahmad', 'V', 'Jabbar', '987987987', '1959-03-29', 'Dallas, Houston, TX', 'M', 25000,
    '98765432-1', 4),
    ('Alicia', 'J', 'Zelaya', '999887777', '1958-06-19', 'Castle, SPring, TX', 'F', 25000,
    '987654321', 4)
    ;

    INSERT INTO COMPANY.DEPARTMENT values


    ('Headquarters', 1, '888665555', '1971-06-19'),
    ('Administration', 4, '987654321', '1985-01-
    01'), ('Research', 5, '333445555', '1978-05-
    22');
    Here, a conversion from the raw data to a DATE from a string is demonstrated using the CAST operator.
    INSERT INTO COMPANY.DEPENDENT VALUES
    ('123456789', 'Alice', 'F', CAST('31-Dec-78' as DATE), 'Daughter'),
    ('123456789', 'Elizabeth', 'F', CAST('05-May-57' as DATE),
    'Spouse'), ('123456789', 'Michael', 'M', CAST('01-Jan-78' as DATE),
    'Son'), ('333445555', 'Alice', 'F', CAST('05-Apr-76' as DATE),
    'Daughter'), ('333445555', 'Joy', 'F', CAST('03-May-48' as DATE),
    'Spouse'), ('333445555', 'Theodore', 'M', CAST('25-Oct-73' as DATE),
    'Son'), ('987654321', 'Abner', 'M', CAST('29-Feb-32' as DATE),
    'Spouse');

    The rest of the INSERT statements are straightforward and self

    explanatory. INSERT INTO COMPANY.DEPT_LOCATIONS VALUES


    (1, 'Houston'),
    (4, 'Stafford'),
    (5, 'Bellaire'),
    (5, 'Sugarland'),
    (5, 'Houston');

    The data for hours for the hours domain value in the highlighted row below was missing
    from the data. Since the column did not allow nulls, it has been defaulted to a zero
    (0). An alternative would have been to specify a DEFAULT as 0 rather than not
    allowing NULL values.

    INSERT INTO COMPANY.WORKS_ON VALUES


    ('123456789', 1, 32.5),
    ('123456789', 2, 7.5),
    ('333445555', 2, 10),
    ('333445555', 3, 10),
    ('333445555', 10, 10),
    ('333445555', 20, 10),
    ('453453453', 1, 20),
    ('453453453', 2, 20),
    ('666884444', 3, 40),
    ('888665555', 20, 0),
    ('987654321', 20, 15),
    ('987654321', 30, 20),
    ('987987987', 10, 35),
    ('987987987', 30, 5),
    ('999887777', 10, 10),
    ('999887777', 30, 30);

    All INSERT statements ran successfully – all tables are verified for data.

    USE COMPANYDB1;
    select * from DEPARTMENT;
    select * from DEPENDENT;
    select * from
    DEPT_LOCATIONS; select *
    From EMPLOYEE;
    select * from PROJECT;
    select * from
    WORKS_ON;

    You might also like