RELATIONAL DATABASE DESIGN USING SQL STATEMENTS 50 points Group will create a Cl

Question

RELATIONAL DATABASE DESIGN USING SQL STATEMENTS 50 points Group will create a Class Session and Student Enrolment database application using Microsoft Access and relational model. The relational database application will demonstrate the ability of a database to capture, store, and report on data and information through the use of SQL syntax. Specific instructions, concepts, expectations, and demonstrations of coding SQL in Access will be provided during scheduled class lecture time as stated in the syllabus. It is highly recommended that you attend all scheduled class sessions to obtain al the necessary skills and understanding to complete this assignment Your final deliverable for this project is a Microsoft Access File containing a sequence of SQL statements that will recreate the database design from Part 1 of the project in Microsoft Access. Your submission will need to include the following Note: Name all your queries in the Access file with the naming convention that starts with #. Example" I CREATE Session Table", "2 CREATE Student Table. "x Select...,etc Database Entities &Attributes; Requirement: You will use CREATE and ALTER SQL Statements to create the following database structure in Access. Please refer to Access Group Project Part 1 Guideline for details of the Attribute requirements. StudentTable SessionTable SessionTitle ScheduleTable SchedulelD StudentiD StudentEmailAdd SessionMaxCapacity Data Requirement: You will use INSERT Statements to populate the Student Table with the following data. 1 Kan 2 lane 3 Jason 4 Susan 5 Aron 6 Zack Wang Smith Cady Thomas Chen Del Pizzo 234 Street Ave 99 ABC Street kanwang@uor.edu(123) 456-7890 anesmith ucr.edu (293) 749-3274 8372 Crest Lane susanthomas@ucr.ed (238) 746 8237 aronchen ucr.edu (987) 324-8768 9287 Lemon Street 55555-5555

Explanation / Answer

Answer)
SQL statements to create the table

CREATE TABLE SessionTable (
SessionNumber int primary key,
SessionTitle varchar,
SessionDescription varchar,
SessionStartDate date,
SessionEndDate date,
SessionMeetingTime varchar,
SessionMaxCapacity int,
SessionPrice int
);

create table ScheduleTable(
ScheduleID int primary key,
StudentID int,
SessionNumber int,
FOREIGN KEY (StudentID) REFERENCES StudentTable(StudentID),
FOREIGN KEY (SessionNumber) REFERENCES SessionTable(SessionNumber)
);

create table StudentTable (
StudentID int primary key,
StudentFirstName varchar,
StudentLastName varchar,
StudentAddress varchar,
StudentZipCode varchar,
StudentEmaiAdd varchar,
StudentPhoneNo varchar
);

Sample insert SQL statement to each of the tables:

insert into StudentTable values (1,'Kan','Wang','1234 Street Ave', '99999-9999', 'kanwang@ucr.edu','(123)456-7890');

insert into SessionTable values (1,'BUS 173','Database Management',2017-06-27,2018-07-27,'MW 5:40 pm - 8:30 pm', 60, 2500);

insert into ScheduleTable values (1,1,1);

SQL queries for the Requirements:

Q) Sessions having maximum capacity under 60 students:

select SessionNumber,SessionTitle, SessionDescription from SessionTable where SessionMaxCapacity<60;

Q) Session having price of course over 1500 but under 3500:

select SessionNumber, SessionTitle, SessionDescription from SessionTable where SessionPrice between 1500 and 3500;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.