Q1. You are required to create the following tables using SQL CREATE command, ta
ID: 3619574 • Letter: Q
Question
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 MarksStep 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.
Explanation / Answer
Dear, create table MEMBER ( Memberid int, Firstname varchar(20), Lastname varchar(10), DateOfBirth datetime, City varchar(20), ZipcodeEmail varchar(20), DateOfJoining datetime PRIMARY KEY(memberid) ); create table ATTENDANCE ( Meetingdate datetime, Location varchar(10), MemberAttended char(1), Memberid int, FOREIGN KEY(memberid)references MEMBER(Memberid) ); create table CATEGORY ( Categoryid int, Category varchar(20), PRIMARY KEY(Categoryid) ); create table DOCUMENTARY ( Documentaryid int, Documentaryname varchar(20), YearReleased datetime, AvailableOnDVD char(1), Rating int, Categoryid int FOREIGN KEY(Categoryid)references CATEGORY(Categoryid) ); create table FAVOURITE_CATEGORY ( Categoryid int, Memberid int, FOREIGN KEY(Memberid)references MEMBER(Memberid), FOREIGN KEY(Categoryid)references CATEGORY(Categoryid) ); ------------------------------------------------------------------------------------------------------ INSERT INTO MEMBER(Memberid, Firstname, Lastname, DateOfBirth, City, ZipcodeEmail, DateOfJoining) VALUES (101, 'Latha','xyx', 'Jan-10-1969', 'Ramapuram', '01latha@gmail.com', 'Jan-10-1999') INSERT INTO MEMBER(Memberid, Firstname, Lastname, DateOfBirth, City, ZipcodeEmail, DateOfJoining) VALUES (102, 'Swetha','zyx', 'Jan-22-1965', 'Dirchlet', '02swetha@gmail.com', 'Feb-3-1999') INSERT INTO MEMBER(Memberid, Firstname, Lastname, DateOfBirth, City, ZipcodeEmail, DateOfJoining) VALUES (103, 'Ram','xyz', 'Jul-12-1966', 'Komal', '03ram@gmail.com', 'Feb-10-1989') INSERT INTO MEMBER(Memberid, Firstname, Lastname, DateOfBirth, City, ZipcodeEmail, DateOfJoining) VALUES (104, 'Noelle','yzx', 'Feb-8-1965', 'Amway', '04noelle@gmail.com', 'Jul-1-1999') INSERT INTO MEMBER(Memberid, Firstname, Lastname, DateOfBirth, City, ZipcodeEmail, DateOfJoining) VALUES (105, 'Kundan','yxz', 'Aug-11-1962', 'Kazipet', '05kundan@gmail.com', 'Jan-10-1999') ---------------------------------------------------------------------------------------------------- INSERT INTO ATTENDANCE(Meetingdate, Location, MemberAttended, Memberid) VALUES ('Mar-4-1988', 'Ramavaran', 'y', 102) INSERT INTO ATTENDANCE(Meetingdate, Location, MemberAttended, Memberid) VALUES ('Apr-10-1989', 'Ketaresh', 'n', 101) INSERT INTO ATTENDANCE(Meetingdate, Location, MemberAttended, Memberid) VALUES ('May-1-1999', 'Romewel', 'y', 101) INSERT INTO ATTENDANCE(Meetingdate, Location, MemberAttended, Memberid) VALUES ('Aug-8-1999', 'Ketar', 'n', 103) INSERT INTO ATTENDANCE(Meetingdate, Location, MemberAttended, Memberid) VALUES ('Jun-1-1998', 'Kaxal', 'y', 104) -------------------------------------------------------------------------------------------------- INSERT INTO CATEGORY (Categoryid, Category) VALUES (001, 'Agriculture') INSERT INTO CATEGORY (Categoryid, Category) VALUES (002, 'Law') INSERT INTO CATEGORY (Categoryid, Category) VALUES (003, 'Religion') INSERT INTO CATEGORY (Categoryid, Category) VALUES (004, 'Agriculture') INSERT INTO CATEGORY (Categoryid, Category) VALUES (005, 'Human Rights') ------------------------------------------------------------------------------------------------------- INSERT INTO DOCUMENTARY(Documentaryid, Documentaryname, YearReleased, AvailableOnDVD, Rating, Categoryid) VALUES (500, 'AG','1956', 'n', 5, 003) INSERT INTO DOCUMENTARY(Documentaryid, Documentaryname, YearReleased, AvailableOnDVD, Rating, Categoryid) VALUES (502, 'HR','1999', 'y', 4, 002) INSERT INTO DOCUMENTARY(Documentaryid, Documentaryname, YearReleased, AvailableOnDVD, Rating, Categoryid) VALUES (511, 'L','1986', 'y’, 5, 001) INSERT INTO DOCUMENTARY(Documentaryid, Documentaryname, YearReleased, AvailableOnDVD, Rating, Categoryid) VALUES (510, 'W','1945', 'n', 1, 003) INSERT INTO DOCUMENTARY(Documentaryid, Documentaryname, YearReleased, AvailableOnDVD, Rating, Categoryid) VALUES (501, 'R','1936', 'n', 3, 002) ----------------------------------------------------------------------------------------------------- INSERT INTO FAVOURITE_CATEGORY(Categoryid, Memberid) VALUES (001, 102) INSERT INTO FAVOURITE_CATEGORY(Categoryid, Memberid) VALUES (003, 102) INSERT INTO FAVOURITE_CATEGORY(Categoryid, Memberid) VALUES (002, 101) INSERT INTO FAVOURITE_CATEGORY(Categoryid, Memberid) VALUES (001, 103) INSERT INTO FAVOURITE_CATEGORY(Categoryid, Memberid) VALUES (002, 105)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.