Note that for the sales transaction relation

Dr Dave Pitts	CS610AH2: Database Management	Spring 2014

Assignment 1

1. Consider the following scenario: a company has a number of customers who have addresses and phone numbers and we assume that while their may be multiple customers with the same address, no two customers with the same name have the same address or the same phone number. Each customer has a customer id, which is unique to that customer. Suppose that this information is stored as a relational database relation or table. Answer the following:

2. Consider the previous scenario. Suppose now that the company wants to also build relations that describes its inventory and its sales transactions. For the inventory, the store keeps track of the following:

◦ the product manufacturer

For the sales transactions, the company tracks the following

◦ the customer making the sale

Develop the schemas for each of the above relations, including primary keys. Note that for the sales transaction relation, you must develop foreign keys that refer to the customer table and the inventory table.

3. Suppose that company created a unique transaction id for each customer purchase. How would that change your schema for the sales transaction table in the previous question in terms of candidate keys?

R1						R2				ClubMem	SID	j
A B C						C D E						s t		f
1 e 4						2 s		11 t				t	f	s
2 g 9				g		4 e		8
						9 h 15 k						d a		w
4.
5.	σE<12(R2)
6.	σA=1∨D=e(R1)
7.
8.
9.
10.
11.	R1×R3
12.	σR1.C=R2.C ∧R1.D=R2.D(R1×R2)
13.
14.
15.

SID Name Gender Status GPA
100 Bill		M		Fr	3.3	Clubs				ClubID
			M	So		Clubs
						ClubID Name			Meeting
						ClubID Name			Meeting	1	2
102 Sue		F		Jr	3.2	Room				1	2
102 Sue		F		Jr	3.2		Chess
103 Mary F				Sr	3.6		Chess
105 Jane		F		Fr	2.9	2	Debate 145
105 Jane		F		Fr	2.9	2	Debate 145

2/3

Write the relational algebra expressions that will produce tables with the following information, using the tables Student, Club, ClubMem show above.