cs403 database management mini project July 2010

[asia]

Q1. You are required to create the following tables using SQL CREATE command, table names along with column description is given below. One of the important things is to use appropriate data types for each column definition and choosing primary and foreign keys for the tables. 20 Marks
Step 1:
1. Table Name: MEMBER

i. MemberId (Member Identification Number)
ii. FirstName
iii. LastName
iv. DateOfBirth
v. City
vi. ZipCode Email
vii. DateOfJoining

2. Table Name: ATTENDANCE

i. MeetingDate
ii. Location
iii. MemberAttended Hint: This Column is used to check either the member attend the meeting or not so you can use its data type ‘CHAR(1)’.
iv. MemberId Hint: Behaving as foreign key here in Attendance table.

3. Table Name: DOCUMENTARY

i. DocumentaryId (Documentary Identification Number)
ii. DocumentaryName
iii. YearReleased
iv. AvailableOnDVD Hint: This Column is used to check either the this documentary is available on DVD or not like MemberAttended in ATTENDANCE table.
v. Rating
vi. CategoryId Hint: Behaving as foreign key here in Documentary table.

4. Table Name: CATEGORY

i. CategoryId
ii. Category Hint: This means the documentary is either about People, Agriculture, Law, War, Religion or Human Rights. It can be from any category.
5. Table Name: FAVOURITE_CATEGORY

i. CategoryId Hint: Behaving as foreign key here in favourite_category table.
ii. MemberId Hint: Behaving as foreign key here in favourite_category table.

Step 2:
What to do next???
After creating these tables successfully you are required to enter Five (5) records in each of the tables using SQL’s INSERT command.
Important: Students are supposed to submit screen shots of there work for proof. Screen shot for each and every step is not required; these screen shots should be taken after Inserting records in your designed tables. For Example, SELECT * from Category;