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;

[Xpert]

first you start with this command
create database project
create table MEMBER

Code:

rest discuss with me here.

mileys75:

[mehrosh90]

Anyone who could help ?!?!

[Xpert]

i can help u but you have to discuss it with me

[sobia.humtum]

koi mera saath cs403 ki assignment discuss kara

[Xpert]

ok lets discuss i am mcs student and i am in final semester. lets start. that i have given two commands one for creating database and one for table. so now what next

[ayb]

first you start with this command

create database project
create table MEMBER

mileys75:

Xpert bhai meri to ye first two lines main hi error aa jata hy

image dekh k btein kia error hy

[Xpert]

dear app nay , dala howa hai yah nahen ata. aur start main ; termination bhi nahen dali. i suggest you run quries one by one. Like first run the create query and then so on. wuhan par query ko one by one run karnay ka tareqa bhi hai. so try it out for more info please discuss it.

[ayb]

ok
ab dekhien table bna k data enter kia hy kia ye thik hy ???
aur dusri bat PRIMARY KEY ksy lagni hy?

[Xpert]

ok good dear i have seen the image now watch this code and alter it and try to make primary key. we are doing discussion because with this discussion you will learn alot.

Code:

< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR =fillfactor ] [ON {filegroup | DEFAULT} ] ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression )