Mall Database Relations Employees( eid: integer, name: String, position: String,
ID: 3704054 • Letter: M
Question
Mall Database Relations Employees( eid: integer, name: String, position: String, salary: double ): 15] Employs( store: String, eid: integer): [201 Stores(name: String, location: String ): [4] ltems( item: String, brand: String, price: double : 15] Inventory( name: String, item: String, quantity: integer ): [25] Customers(name: String, address: String, aid: integer ): [5] Transactions( tid: integer, store: String, item: String, customer: String, date: Date ): [30] balance: double, type: String ): 7; type must be one of credit, checking, or cash ] Exercises [15 pts] Modify the database Mall to use the relations given here. Note, these relations are based upon the solution to Hw #7, but they are slightly different. 1. [10 pts] Populate each table in the database with at least the number of tuples (record entries) given in the brackets following each relation. 2.Explanation / Answer
create table Employees(Eid integer primary key,name char(30),position char(20),salary double);
insert into Employees(Eid,name,position,salary) values(1,'aa','manager',20000);
insert into Employees(Eid,name,position,salary) values(2,'bb','manager',20000);
insert into Employees(Eid,name,position,salary) values(3,'cc’,’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(4,'dd', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(5,'ee', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(6,'ff’,’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(7,'gg', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(8,'hh', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(9,'ii’,’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(10,'jj', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(11,’kk', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(12,'ll’,’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(13,’mm', ’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(14,'nn’,’salesman’,10000);
insert into Employees(Eid,name,position,salary) values(15,'oo','salesman',10000);
Eid
name
Position
salary
1
aa
Manager
20000
2
bb
Manager
20000
3
cc
Salesman
10000
4
dd
Salesman
10000
5
ee
Salesman
10000
6
ff
Salesman
10000
7
gg
Salesman
10000
8
hh
Salesman
10000
9
ii
Salesman
10000
10
jj
Salesman
10000
11
kk
Salesman
10000
12
ll
Salesman
10000
13
mm
Salesman
10000
14
nn
Salesman
10000
15
oo
Salesman
10000
create table employs(store char(20) primary key,Eid integer foreignkey references Employees(Eid));
insert into employs(store,Eid) values ('a',1);
insert into employs(store,Eid) values ('b',2);
insert into employs(store,Eid) values ('c',3);
insert into employs(store,Eid) values ('d',4);
Store
Eid
A
1
B
2
C
3
D
4
create table Stores(name char(20) primary key,location char(20));
insert into Stores(name,location) values ('a','washington');
insert into Stores(name,location) values ('b','USA');
insert into Stores(name,location) values ('c','UK');
insert into Stores(name,location) values ('d','washington DC');
Number of Records: 4
name
Location
a
Washington
b
USA
c
UK
d
washington DC
create table Items(item char(20) primary key,brand char(20),price double);
insert into Items(item,brand,price) values ('cap','nike',20.3);
insert into Items(item,brand,price) values ('track','puma',200.3);
insert into Items(item,brand,price) values ('shirt','twills',500.3);
Number of Records: 3
item
Brand
price
cap
Nike
20.3
track
Puma
200.3
shirt
Twills
500.3
create table Inventory(name char(20) primary key,item char(20) references Items(item),quantity integer);
insert into Inventory(name,item,quantity) values ('a','caps',20);
insert into Inventory(name,item,quantity) values ('b','trackss',15);
insert into Inventory(name,item,quantity) values ('c','shirts',50);
Number of Records: 3
name
item
quantity
a
caps
20
b
trackss
15
c
shirts
50
create table Accounts(aid integer primary key,balance double,type char(20));
insert into Accounts(aid,balance,type) values (1,200,'debit');
insert into Accounts(aid,balance,type) values (2,300,'debit');
insert into Accounts(aid,balance,type) values (3,3000,'debit');
Number of Records: 3
aid
Balance
type
1
200
debit
2
300
debit
3
3000
debit
create table Customers(name char(20) primary key,address char(20),aid integer references Accounts(aid));
insert into Customers(name,address,aid) values ('abc','washington',1);
insert into Customers(name,address,aid) values ('efg','washington DC',2);
insert into Customers(name,address,aid) values ('hij','USA',3);
Number of Records: 3
name
Address
aid
abc
Washington
1
efg
washington DC
2
hij
USA
3
create table Transactions(tid integer primary key,store char(20),item char(20),customer char(20),Date date);
insert into Transactions(tid,store,item,customer,Date) values (1,'x','cap','abc',1987-03-20);
insert into Transactions(tid,store,item,customer,Date) values (2,'x','cap','abc',1995-03-18);
insert into Transactions(tid,store,item,customer,Date) values (3,'y','shirt','efg',1986-03-18);
Number of Records: 3
tid
store
item
customer
Date
1
x
cap
abc
1987
2
x
cap
abc
1995
3
y
shirts
efg
1986
Eid
name
Position
salary
1
aa
Manager
20000
2
bb
Manager
20000
3
cc
Salesman
10000
4
dd
Salesman
10000
5
ee
Salesman
10000
6
ff
Salesman
10000
7
gg
Salesman
10000
8
hh
Salesman
10000
9
ii
Salesman
10000
10
jj
Salesman
10000
11
kk
Salesman
10000
12
ll
Salesman
10000
13
mm
Salesman
10000
14
nn
Salesman
10000
15
oo
Salesman
10000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.