Write the SQL Script and Relationships (In 3NF) for the following Entity class D

Question

Write the SQL Script and Relationships (In 3NF) for the following Entity class Diagram:

Materials Required WrkOrasklD (PK, FK) MatD (PK, FK) Mat EstQuant MatAct Quant Materials MatD (PK) MatName MatDescript MatUnitCost Mat Stock Custo mers CustID (PK) Cust FName Cust MName Cust LName Cust Phone CustWeb Address Cust StAddress CustCity Cust State Custzip Cust Contact Work Order Tasks WrkOraskID (PK) 1...1 WrkOnNum (FK) TaskID (FK) WrkOrTask Status Wrkor TaskEst Hours Wrkor Task Act Hours WrkorTaskCompDate Tasks TasklD (PK) TaskName Task HrRate Projects ProID (PK) ProName ProDescript ProStartDate ProCompDate ProValue ProStAddress ProCity Prostate ProZip CustID (FK) WorkOrders WrkorNum (PK) 1...1 WrkorCreateDate Wrkor TargCompDate WrkorActComp Date Pro/D (FK)

Explanation / Answer

The below are the sql statements for the above Entity class diagram. In order to maintin the above diagram in 3 normal form we need to do certain changes in customers and projects table. And the remaining all re fine. If a table is said to be in 3 normal form there must be no transitive dependency i.e. no other attribute in the relation will be dependent of non primekey attribute.

Here in the customers and projects table have transtive dependency function so that to remove that we need to seperate address,city,state from both the table and need to be created as seperate relation with the zipcode as primary key.

The SQL statements goes below.

1. create table customers (custid number(5) primary key,cfname varchar2(10),cmname varchar2(10),clname varchar2(10), cphone number(10), cwebaddress varchar2(15),czip number(6),ccontact number(10));

2. create table caddress(czip varchar2(6) primary key, caddress varchar2(10),ccity varchar2(5),cstate varchar2(5));

3. create table projects(proid number(5) primary key,pname varchar2(10),pdescription varchar2(10),pstartdate date,pcompdate date,pvalue number(6),pzip number(5),custid number(5), foreign key (custid) references customer(custid));

4. create table paddress(pzip number(5) primary key,pstaddress varchar2(10),pcity varchar2(10),pstate varchar2(10));

5. create table workorders (wrkOrNum number(5) primary key,wocreatedate date,wotargcompdate date,proid number(5) ,foreign key (proid) references projects(proid));

6.create table wotasks(wotaskid number(5) primary key,wonumber number(5), taskid number(5),wotaskstatus varchar2(5),wotaskesthours number(3),wotaskacthours number(3),wotaskcompdate date,foreign key( wonumber) references workorders(wonumber),foreign key(taskid) references tasks(taskid));

7. create table tasks(taskid number(5) primary key, taskname varchar2(10),taskrate number(4));

8. create table matrequired(wotaskid number(5) ,matid number(5),matestquant number(5),matactquantity number(5), primary key(wotaskid,matid), foreign key(wotaskid) references wotasks(wotaskid),foreign key(matid) references materials(matid));

9. create table materials(matid number(5) primary key,matname varchar2(10),matdescription varchar2(10),matunitcost number(5),matstock number(5));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.