SLP 3 CSC
rick07
SLP3_CSC316.docx
Page 48of88
Continue this bookstore database (tables) below. Here are your tasks:
1. Normalize the tables. Discuss 1NF, 2NF and 3NF, and whether you believe your tables satisfy each level of normal forms. If all your tables have satisfied the 3NF, explain the reason.
2. Drop the tables no longer needed, and create tables as required in your new design, using SQL in DBMS.
Include the SQL statements and screenshots of your new tables in the paper.
Entity Set Description
CLIENT:
Name
Cust_ID
Name
Phone
ValueRange
Positive
number
ASCII
ASCII
DateLastTrans
Last shopping date.
Date
Any date equal to or greater than DateCreated
DateCreated
Date joined, first trans.
date
Valid
numericchars
Anydate
beforepresent
Default
Value
NULL
Allowed?Unique?
Singleor Multi-value?
SimpleorComposite?
Next
available
Passedvalue
null
null
null
Passed
value
NO
NO
YES
YES
YES
NO
YES
NO
NO
NO
NO
NO
Single
Single
Single
Single
Single
Single
Simple
Simple
Simple
Simple
Simple
Simple
|
Description |
PK |
name |
Cellphone |
|
|
Domain/Type |
Number(9) |
Varchar(128) |
Number(10) |
Varchar(128) |
Candidate keys: Cust_ID,Name, Phone,Email
Primary key:Cust_ID
Weak/Strong: strong
Fieldstobeindexed:Cust_ID
DEPENDENTS:
|
Name |
Bdate |
Name |
Relationship |
|
DateCreated |
Emp_ID |
|
DescriptionDomain/Type ValueRange
DefaultValue NULL Allowed?Unique? |
Birthdate date Anydatebeforepresent
null YESNO |
Employeename Varchar(128)ASCII Passedvalue NO NO |
Relationtoemployee Varchar(128)ASCII Passedvalue NO NO |
emailVarchar(128) ASCII
null YESNO |
Dateadded
date Anydatebeforepresent Autogenerated NO NO |
ID ofEmployee Number(7)integer
Passed value NO
NO |
|
Singleor Multi-value? SimpleorComposite? |
single
simple |
Single
Simple |
Single
Simple |
Single
Simple |
Single
Simple |
Single
Simple |
Candidate keys:none
Primary key:none
Weak/Strong:weak
Partial key:Name
Fields to be indexed:Name
1 | Page
ELECTRONIC_EMPLOYEES:
|
Name |
Emp_NUM |
Name |
SSN |
Phone |
Electronic_ITEMS_ID |
Date_Start |
|
Description |
PK |
name |
Socialsecurity# |
Phonenumber |
Foreign Key fromElectronic_ITEMStable |
Date of employment |
|
Domain/Type |
Number(7) |
Varchar(128) |
Number(9) |
Number(10) |
Number(3) |
Date |
|
ValueRange |
1 … 9999 |
ASCII |
ASCII |
ASCII |
1 … 999 |
Date |
|
DefaultValue |
Next available |
Passed value |
Passed value |
null |
FKvalue |
Current |
|
NULL Allowed? |
NO |
NO |
NO |
YES |
YES |
NO |
|
Unique? |
YES |
NO |
YES |
NO |
NO |
NO |
|
Single or Multi-value? |
single |
Single |
Single |
Single |
Single |
Single |
|
Simple or Composite? |
simple |
Simple |
Simple |
Simple |
Simple |
Simple |
ELECTRONIC_EMPLOYEES:
|
Name |
Address |
PayType |
Password |
|
Pay |
Date_End |
|
Description |
Home address |
0=Salaried &1=hourly |
User password |
|
Amount they make |
Date |
|
Domain/Type |
Varchar(128) |
Number(1) |
Varchar(128) |
Varchar(128) |
Number(9) |
Date |
|
ValueRange |
ASCII |
0 or 1 |
ASCII |
ASCII |
1…99999999 9 |
Date |
|
DefaultValue |
Null |
Passedvalue |
Passedvalue |
null |
Null |
NULL |
|
NULL Allowed? |
YES |
NO |
YES |
YES |
YES |
YES |
|
Unique? |
NO |
NO |
NO |
NO |
NO |
NO |
|
Singleor Multi-value? |
Single |
single |
Single |
Single |
Single |
Single |
|
SimpleorComposite? |
Simple |
simple |
Simple |
Simple |
Simple |
Simple |
Candidatekeys:Emp_ID,SSN,Email,Name
Primarykey:Emp_ID
Weak/Strong:strong
Emp_IDCHECKOUT:
Name
Checkout_ID
Cust_ID
Date
Store_ID
Subtotal
Emp_ID
Tax
Description
Specific checkout number
FKfrom CustomerID
Date of checkout
Foreign key
from Store table
Amount to fall items on list
Employee who performed checkout
Tax amount on subtotal
Domain/Type
Number(9)
Number(7)
date
Number(3)
double
Number(7)
double
ValueRange
integer
integer
Present date
integer
Positivenumber
integer
Positive number
DefaultValue
Nex tavailable
FKvalue
Auto generated
FKvalue
null
FKvalue
null
NULL
Allowed?
NO
NO
NO
NO
NO
NO
NO
Unique?
YES
YES
NO
YES
NO
YES
NO
Singleor Multi-value?
single
Single
Single
Single
Single
Single
Single
SimpleorComposite?
simple
Simple
Simple
Simple
Simple
Simple
Simple
Candidatekeys: Checkout_ID,Cust_ID,Electronic_ITEMS_ID
Primarykey: Checkout_ID
Weak/Strong:strong
Fields to be indexed:Checkout_ID
ELECTRONIC_ITEMS:
|
Name |
Item_ID |
Brand |
Description |
Price |
Cost |
|
Description |
Sequence- UniqueinternalID# |
Brandofitem |
Itemdescription |
Currentprice |
Whole sale cost |
|
Domain/Type |
Number(7) |
Varchar(32) |
Varchar(128) |
Double |
Double |
|
ValueRange |
integer |
ASCII |
ASCII |
Positive number |
Positive number |
|
DefaultValue |
Next available |
Passed value |
Passed value |
Passed value |
Passed value |
|
NULL Allowed? |
NO |
NO |
NO |
NO |
NO |
|
Unique? |
YES |
NO |
NO |
NO |
NO |
|
Singleor Multi-value? |
single |
Single |
Single |
Single |
Single |
|
SimpleorComposite? |
simple |
Simple |
Simple |
Simple |
Simple |
ELECTRONIC_ITEMS: (continued)
|
Name |
Shape |
Size |
UPC |
Weight |
Taxable |
|
Description |
Basicshapedescription |
Basic size description |
ManufacturesID |
Currentweight |
Istheitemtaxed |
|
Domain/Type |
Varchar(32) |
Varchar(32) |
Number(9) |
Number(4,2) |
integer |
|
ValueRange |
ASCII |
ASCII |
Positive number |
Positive number |
1 for yes 0 for no |
|
DefaultValue |
Null |
Null |
Passedvalue |
null |
1 |
|
NULL Allowed? |
YES |
YES |
NO |
YES |
NO |
|
Unique? |
NO |
NO |
YES |
NO |
NO |
|
Singleor Multi-value? |
single |
single |
Single |
Single |
Single |
|
SimpleorComposite? |
simple |
simple |
Simple |
Simple |
Single |
Candidate keys: Item_ID,UPC
Primarykey:Item_ID,UPC
Weak/Strong:strong
Fieldstobeindexed:Item_ID,UPC
|
Name |
Electronic_ITEMS_ID |
Address |
|
Description |
Sequence-UniqueinternalID# |
Physicallocation |
|
Domain/Type |
Number(3) |
Varchar(128) |
|
ValueRange |
integer |
ASCII |
|
DefaultValue |
Nextavailable |
Passedvalue |
|
NULL Allowed? |
NO |
NO |
|
Unique? |
YES |
NO |
|
Singleor Multi-value? |
single |
Single |
|
SimpleorComposite? |
Simple |
Simple |
Candidatekeys:Electronic_ITEMS_ID
Primarykey:Electronic_ITEMS_ID
Weak/Strong:strong
Fieldstobeindexed:Electronic_ITEMS_ID
Page14of88
STORE:
|
Name |
Electronic_ITEMS_ID |
Item_ID |
Quantity |
|
Description |
WhatElectronic_ITEMSisitin? |
Whatitemisininventory? |
Howmany? |
|
Domain/Type |
Number(3) |
Varchar(128) |
Number(5) |
|
ValueRange |
integer |
ASCII |
Positivenumber |
|
DefaultValue |
FKfromElectronic_ITEMStable,ID. |
FKfromItemtable,ID. |
Passedvalue |
|
NULL Allowed? |
NO |
NO |
NO |
|
Unique? |
NO |
NO |
NO |
|
Singleor Multi-value? |
single |
Single |
Single |
|
SimpleorComposite? |
simple |
Simple |
Simple |
Candidatekeys: Electronic_ITEMS_ID,Item_IDPrimarykey:Electronic_ITEMS_ID,Item_IDWeak/Strong:strong
Fieldstobeindexed:Electronic_ITEMS_ID,Item_ID
Notes:Electronic_ITEMS_IDisneededheretotietheElectronic_ITEMStothequantityforreportsthatareElectronic_ITEMSspecific
Page15of88
|
Name |
Checkout_ID |
Quantity |
Item_ID |
|
Description |
Specificcheckoutnumber. |
NumberofElectronic_ITEMSpurchasedonthattransaction. |
Specificitemthatwascheckedout. |
|
Domain/Type |
sequence |
Integer |
sequence |
|
ValueRange |
1 … 999999 |
Positivenumber |
1 … 9,999,999 |
|
DefaultValue |
FKfromCheckouttable,ID. |
Passedvalue |
FKfromElectronic_ITEMStable,ID. |
|
NULL Allowed? |
NO |
YES |
NO |
|
Unique? |
NO |
NO |
NO |
|
Singleor Multi-value? |
Single |
Single |
Single |
|
SimpleorComposite? |
Simple |
Simple |
Simple |
Candidatekeys:Checkout_ID,Item_IDPrimarykey:Checkout_ID,Item_IDWeak/Strong:strong
Fields to be indexed: Checkout_ID,Item_ID
Page16of88
CHECKOUTACTION:
MANAGER
|
Name |
Electronic_ITEMS_ID |
Emp_ID |
Position |
|
Description |
Specificcheckoutnumber. |
FKfromEmployeetable |
LevelofmanagementattheElectronic_ITEMS. |
|
Domain/Type |
sequence |
Number |
Number |
|
ValueRange |
1 … 999 |
1 … 9999999 |
1 … 999 |
|
DefaultValue |
FKfromCheckouttable,ID. |
FKfromEmployeetable |
1 |
|
NULL Allowed? |
NO |
NO |
NO |
|
Unique? |
NO |
NO |
NO |
|
Singleor Multi-value? |
Single |
Single |
Single |
|
SimpleorComposite? |
Simple |
Simple |
Simple |
Candidate keys: Checkout_ID,Item_ID
Primary key:Checkout_ID,Item_ID
Weak/Strong:strong
Fields to be indexed: Checkout_ID,Item_ID
Page 18of88
E-RDiagram
Sample Relation Instances
Clients
|
Cust_ID |
Name |
Phone |
|
DateCreated |
DateLastTrans |
|
150 |
BobSuchi |
2612552455 |
null |
2001-1-1 |
2016-5-7 |
|
151 |
Namita |
4788754568 |
[email protected] |
2005-5-5 |
2016-4-25 |
|
152 |
Scottnidhi |
4776521465 |
[email protected] |
2011-12-12 |
2015-3-4 |
|
153 |
neha agarawal |
NULL |
[email protected] |
2008-8-12 |
2014-5-9 |
|
158 |
Gita |
23232345 |
[email protected] |
2001-6-6 |
2010-8-25 |
|
167 |
Kavita |
5619755866 |
[email protected] |
1999-4-9 |
2010-4-6 |
|
199 |
ScottNeha |
4586875837 |
[email protected] |
2000-1-9 |
2011-10-10 |
|
205 |
ShakeNidhi |
4578905545 |
[email protected] |
2000-8-25 |
2017-8-18 |
|
278 |
Wayneshalini |
5619872345 |
null |
2000-1-9 |
2013-12-5 |
|
279 |
Monik |
57895812154 |
Null |
2001-5-20 |
2012-8-18 |
Dependents
|
Bdate |
Name |
Relationship |
|
DateCreated |
Emp_ID |
|
1995-5-12 |
Bmmm |
Husband |
NULL |
2011-2-30 |
1 |
|
2011-7-23 |
D |
Son |
NULL |
2011-4-20 |
2 |
|
NULL |
Soha |
Daughter |
[email protected] |
2011-4-20 |
2 |
|
NULL |
Salman |
Husband |
null |
2015-5-6 |
3 |
|
2004-6-24 |
Neh |
Son |
null |
2010-9-2 |
4 |
|
19890-2-9 |
Je |
Wife |
[email protected] |
1990—8-6 |
6 |
|
1992-8-2 |
Amina |
Wife |
NULL |
2011-9-7 |
8 |
|
2008-9-9 |
Be |
Daughter |
NULL |
2011-11-5 |
9 |
|
2008-1-8 |
L |
Wife |
Null |
2011-7-9 |
9 |
Page 23of88
Electronic_EMPLOYEES
|
Emp_ID |
Name |
SSN |
Phone |
Pay |
Date_End |
Electronic_ITEMS_ID |
|
1 |
DarrelPhilbin |
654269856 |
5489659874 |
20 |
2011-2-2 |
854 |
|
2 |
RickyTanner |
125651452 |
6988532587 |
10 |
1999-6-10 |
354 |
|
3 |
SusanPhillips |
145969658 |
9856984523 |
15 |
NULL |
696 |
|
4 |
GeorgeScott |
147589652 |
2586521452 |
42000 |
NULL |
159 |
|
5 |
ErinAbernathy |
256985698 |
5896583541 |
30 |
NULL |
674 |
|
6 |
TedSmith |
352956587 |
4736593569 |
50000 |
NULL |
369 |
|
7 |
HarryButs |
458521658 |
2586584763 |
12 |
NULL |
778 |
|
8 |
MaynarTeener |
256656521 |
2596573257 |
9.25 |
NULL |
989 |
|
9 |
MattLongfellow |
958786548 |
5249868525 |
60000 |
NULL |
247 |
|
10 |
JerryGarcia |
758965897 |
6521458569 |
52000 |
NULL |
348 |
|
11 |
LawarnceTom |
625458569 |
9658745632 |
15 |
2011-9-0 |
348 |
|
12 |
DexterRobert |
254125478 |
1111111111 |
12.25 |
NULL |
854 |
|
13 |
MarkNick |
563258965 |
2225478512 |
8.25 |
NULL |
854 |
|
14 |
JeremyDavid |
111111112 |
2356895654 |
16000 |
NULL |
159 |
|
15 |
LukeTed |
111111144 |
2144544123 |
20000 |
NULL |
778 |
Electronic_EMPLOYEEScontinue
|
Address |
Pay_Type |
Password |
|
Date_Start |
|
Xyz road |
0 |
1234 |
1985-4-5 |
|
|
15 7Hst |
0 |
Abcdef |
1990-6-8 |
|
|
343 hno xux st |
0 |
Password |
1972-6-9 |
|
|
41 Gold street |
0 |
Apass |
NULL |
1999-7-25 |
|
65 golden avenue |
1 |
Bpaa |
[email protected] |
1998-12-20 |
|
132 asaa |
0 |
Work |
NULL |
1989-6-8 |
|
1 wo street |
1 |
Pass |
NULL |
1970-10-20 |
|
43 sss |
0 |
Pass |
null |
2005-6-4 |
|
64 dss |
1 |
Pass |
[email protected] |
2000-9-21 |
|
434ccc |
1 |
1234 |
|
1990-9-24 |
|
245aa |
1 |
NULL |
NULL |
1989-1-20 |
|
312xxx |
0 |
NULL |
NULL |
1990-5-6 |
|
68xxx |
1 |
NULL |
NULL |
1998-2-5 |
|
123 |
0 |
NULL |
NULL |
2000-6-3 |
|
23 mmmm |
1 |
NULL |
NULL |
2004-9-9 |
Checkout
|
Checkout_ID |
Cust_ID |
Emp_ID |
Date |
Electronic_ITEMS_ID |
Subtotal |
Tax |
|
101 |
150 |
2 |
2014-9-9 |
54 |
65.25 |
1.74 |
|
132 |
151 |
2 |
2014-9-6 |
54 |
115.25 |
1.5 |
|
6589 |
152 |
3 |
2012-5-01 |
96 |
66.52 |
.35 |
|
1147 |
199 |
4 |
2011-11-7 |
59 |
500.25 |
2.24 |
|
110 |
279 |
5 |
2012-12-6 |
74 |
41.35 |
.891 |
|
1141 |
105 |
6 |
2007-5-6 |
69 |
64.25 |
2.25 |
|
3652 |
278 |
6 |
2011-11-12 |
78 |
14.25 |
1.12 |
|
225 |
158 |
7 |
2005-12-24 |
89 |
80.85 |
3.12 |
Electronic_ITEMS
|
Item_ID |
Brand |
Description |
Price |
Cost |
|
1 |
N |
Tea maker |
32.25 |
31.00 |
|
65 |
Britania |
Washing machine |
3335.00 |
2223.00 |
|
458 |
Kkkkk |
tv |
3236.00 |
224.00 |
|
236 |
Parle |
cattle |
221.99 |
.22250 |
|
8485 |
Zzz |
e-cooker |
2222.50 |
3434 |
|
352 |
Samsuung |
mobile |
2224.00 |
2342.00 |
|
355 |
LG |
TV |
221.99 |
123.50 |
|
156 |
De |
smartphome |
.11150 |
.11110 |
|
25 |
He |
Candy maker |
1113.99 |
111.00 |
|
14 |
Kl |
Tea maker |
112.99 |
111.00 |
Electronic_ITEMS
|
Electronic_ITEMSID |
Address |
|
54 |
22556ElmSt |
|
154 |
820BirchRd |
|
96 |
710EdisonDr |
|
59 |
13636FirSt |
|
74 |
14496MapleWay |
|
69 |
940GreenSt |
|
78 |
341MainSt |
|
89 |
25459AspenBlvd |
|
47 |
13695AlderSt |
|
48 |
650BeechSt |
ManagesFor
|
FK_Electronic_ITEMS ID |
FK_Emp_ID |
Position |
|
54 |
1 |
Director |
|
154 |
2 |
Director |
|
96 |
3 |
Director |
|
59 |
4 |
Director |
|
74 |
5 |
Director |
|
69 |
6 |
Director |
|
78 |
7 |
Director |
|
89 |
8 |
Director |
Inventory
|
FK_Electronic_ITEMS ID |
FK_ItemID |
Quantity |
|
854 |
12 |
10 |
|
854 |
658 |
10 |
|
354 |
1566 |
4 |
|
696 |
12 |
23 |
|
696 |
658 |
38 |
|
159 |
335 |
27 |
|
159 |
1566 |
31 |
|
674 |
4587 |
23 |
|
674 |
2365 |
28 |
SELECT * FROM TMJ_CHECKOUT;
DESC TMJ_CHECKOUT;
SELECT * FROM TMJ_CHECKOUT_ACTION;
DESC TMJ_CHECKOUT_ACTION;
SELECT * FROM TMJ_CLIENT;
DESC TMJ_CLIENT;
SELECT * FROM TMJ_DEPENDANTS;
SELECT * FROM TMJ_INVENTORY;
DESC TMJ_INVENTORY;
DESC TMJ_ELECTRONIC_EMPLOYEES;
Page45of88
DESC TMJ_DEPENDANTS;
SELECT * FROM TMJ_ELECTRONIC_EMPLOYEES;
Page 46of88
DESC TMJ_ELECTRONIC_ITEMS;
DESC TMJ_MANAGESFOR;
DESC TMJ_ELECTRONIC_ITEMS;
