(Information system and technology) see below

Running head: Database Work

5

Database

Database Work

Student Name:

Instructor Name:

Course:

Date:

Question A

CREATE DOMAIN SID CHAR(6);

CREATE DOMAIN NAME CHAR(30);

CREATE DOMAIN CATEGORY CHAR(20);

CREATE DOMAIN DISCOUNT NUMERIC(6);

CREATE DOMAIN QTY NUMERIC(10);

CREATE DOMAIN UNITPRICE NUMERIC(20);

CREATE DOMAIN EXTENDPRICE NUMERIC(20);

CREATE DOMAIN PHONE NUMERIC(30);

CREATE DOMAIN OWNERID CHAR ( 7 );

CREATE BASE RELATION SUPPLIER

( SID DOMAIN ( SID ),

SNAME DOMAIN ( NAME ))

PRIMARY KEY ( SID);

CREATE BASE RELATION OWNER

( OWNERID DOMAIN ( OWNERID ),

PROJECTNAME DOMAIN ( NAME ),

OWNERNAME DOMAIN ( NAME ))

PRIMARY KEY ( OWNERID, PROJECTNAME );

CREATE BASE RELATION PARTS

( SID DOMAIN ( SID ),

PARTNAME DOMAIN ( NAME ),

CATEGORY DOMAIN ( CATEGORY ),

DISCOUNT DOMAIN ( DISCOUNT ),

QTY DOMAIN ( QTY ),

UNITPRICE DOMAIN ( UNITPRICE ),

EXTENDPRICE DOMAIN (EXTENDPRICE))

PRIMARY KEY ( PID);

Question B

Functional dependencies for relations are shown below:

a. SUPPLIER relation:

SID -> SNAME

b. OWNER relation:

OWNERID -> OWNERNAME

c. PARTS relation:

(QTY, UNITPRICE) -> (DISCOUNT, EXTENDPRICE)

Question C

The third normal form requires that the relations be in first and second normal forms as well as having no transitive dependencies.

The relations in 3NF are illustrated below.

First normal form ( get rid of duplicates and redundant data)

OWNER ( OWNERID, OWNERNAME, PROJECTNAME)

PARTS ( PID, PARTNAME, SID, CATEGORY, DISCOUNT, QTY, UNITPRICE, EXTENDPRICE)

SUPPLIER ( SID, SNAME, PARTNAME)

Second normal form (remove partial dependencies)

OWNER ( OWNERID, OWNERNAME)

PROJECT ( PROJECTNAME )

PROJOWNER ( OWNERID, PROJECTNAME )

SUPPLIER ( SID, SNAME )

PARTS ( PID, PARTNAME, CATEGORY, DISCOUNT, QTY, UNITPRICE, EXTENDPRICE)

SUPPARTS ( SID, PID )

Third normal form ( remove transitive dependencies)

OWNER ( OWNERID, OWNERNAME)

PROJOWNER ( OWNERID, PROJECTNAME )

PROJECT ( PROJECTNAME )

SUPPLIER ( SID, SNAME )

SUPPARTS ( SID, PID )

PARTS ( PID, PARTNAME, CATEGORY, UNITPRICE)

GETDISCOUNT ( PID, UNITPRICE, QTY, DISCOUNT, EXTENDPRICE)

Question D

Final relations with data:

OWNER relation

OWNERID	OWNERNAME
1	Elizabeth
2	John
3	Ralph

PROJOWNER relation

OWNERID	PROJECTNAME
1	Highland House
2	Baker Remodel
3	Hew Remodel

PROJECT relation

PROJECTNAME

Highland House

Baker Remodel

Hew Remodel

SUPPLIER relation

SID	SNAME
232	NW Electric
222	EB Supplies
110	Contractor
199	Interior, Inc

PARTS relation with sample data

PID	PARTNAME	CATEGORY	UNITPRICE
77	200A Panel	Electrical	170.00
78	50W Braker	Electrical	60.00
79	20W Braker	Electrical	35.00
80	15W Braker	Electrical	35.00
81	12ga, 3W / F	Electrical	1.50
82	Sliding	Exterior	22.50

GETDISCOUNT relation

PID	UNITPRICE	QTY	DISCOUNT	EXTENDPRICE
77	170.00	1	25%	170.00
78	60.00	3	25%	180.00
79	35.00	7	25%	245.00
80	35.00	15	25%	525.00