SQL-HWSolution

SQL-HWSolution - Part I, Data Definition 1. Create the...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Part I, Data Definition 1. Create the following tables and load the data into the tables using DDL statements in Microsoft SQL Server. Do not use the GUI. Decide on the appropriate formatting for fields on your own. Specify domain constraints as you see fit. Define the tables so that the key, entity, and referential integrities are enforced. Foreign Keys: In Department: Restrict Updates, Restrict Deleltes In Employee: Cascade Updates, Restrict Deletes In Assignment: Restrict Updates, Restricts Deletes for both FKs. Department (DName DMgr ^ DBudget) Accounting E2 110,000 MIS E3 220,000 Production E6 540,000 Procurement E8 80,000 Employee (ENo EName ESalary EDept ^ ) E1 Jones 40,000 MIS E2 Smith 60,000 Accounting E3 Date 55,000 MIS E4 Codd 42,000 Production E5 Brown 48,000 Accounting E6 Green 63,000 Production E7 Chen 60,000 Accounting E8 Blues 40,000 Procurement E9 White 42,000 Procurement Project (PID PBudget) Assignment (ENo ^ PID ^ PctTime) P1 10,000 E1 P1 20 P2 15,000 E1 P2 30 P3 12,000 E1 P3 30 E2 P2 60 E5 P1 40 E6 P1 30 E6 P2 10 E6 P3 40 A Note for Item 1 of Part 1: Department and Employee reference each other. Hence, you cannot load the intial data while the referential integrity constraints are in effect. (Why?) You must specify the constraints after the initial load.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Solution: /**** Creation of Department without foreign key ******/ /*----------------------------------------------------*/ CREATE TABLE Department ( DName CHAR ( 20 ) NOT NULL, DMgr CHAR ( 3 ), DBudget DECIMAL ( 10 , 2 ), PRIMARY KEY ( DName )); /**************** Creation of Employee ****************/ /*----------------------------------------------------*/ CREATE TABLE Employee ( ENo CHAR ( 3 ) NOT NULL, Ename CHAR ( 20 ), ESalary DECIMAL ( 8 , 2 ), EDept CHAR ( 20 ), PRIMARY KEY ( ENo ), FOREIGN KEY ( EDept ) REFERENCES Department ON DELETE NO ACTION ON UPDATE CASCADE ); /**************** Creation of Project ****************/ /*----------------------------------------------------*/ CREATE TABLE Project ( PID CHAR ( 3 ) NOT NULL, PBudget DECIMAL ( 10 , 2 ), PRIMARY KEY ( PID )); /**************** Creation of Assignment **************/ /*----------------------------------------------------*/ CREATE TABLE Assignment ( ENo CHAR ( 3 ) NOT NULL, PID CHAR ( 3 ) NOT NULL, PctTime SMALLINT , PRIMARY KEY ( Eno , PID ), FOREIGN KEY ( ENo ) REFERENCES Employee ON DELETE NO ACTION ON UPDATE NO ACTION , FOREIGN KEY ( PID ) REFERENCES Project ON DELETE NO ACTION ON UPDATE NO ACTION ); Created the data records using “Open table” option. /*********** Adding Foreign key to Department *********/
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 05/01/2008 for the course INFO 628 taught by Professor Choobineh during the Spring '07 term at Texas A&M.

Page1 / 14

SQL-HWSolution - Part I, Data Definition 1. Create the...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online