Description
This homework gives students opportunity to practice the development of a database using MySQL.
Section 1
Use SQL to create tables according to given schema shown in Figure 1. You must use exactly the same names for tables and attributes as the ones in Figure 1. Define a reasonable data type for each attribute. Make sure the types you define have no conflict with the queries in Section 3.
The ER diagram is shown in Figure 2.
Course
CourseNo CourseName Department
Section 
CourseNo SectionNo Instructor
SSN SectionNo CourseNo
Student
SSN FirstName LastName Street City State Zip
Takes
SSN CourseNo SectionNo ExamNo Result
CourseNo SectionNo ExamNo E_Date E_Time
CouductedIn
RoomNo Building CourseNo SectionNo ExamNo
ClassRoom
RoomNo Building Capacity
RoomNo Building CourseName
CourseNo
RoomID Capacity
Department
-
CLASSROOM
COURSE
N
1
Conducted in
Has Section
M
N
ExamNo
Has Exam
SECTION
SectionNo
EXAM
E_Date
M
1
M
Instructor
E_Time
Enrolls
N
N
Takes
M
Zip
STUDENT
State
Address
SSN
name
City
Result
Street
First Name
Last Name
Figure 2 ER diagram
Section 2
Populate the database based on the details specified in Section 3.
Section 3
Use SQL to specify the following queries. When you populate the database, insert data such that at least one row will be display as the result of running each query.
-
Retrieve information (SSN, first name and last name) about students who take the course ‘Database Management Systems’.
-
Retrieve the courses (List course name and number of sections) that have more than 2 sections.
-
List each course number, course name and section number which have 3 or more students in the section.
-
Retrieve the information (SSN, first name, and last name) of students who got Grade ‘A’ in at least 3 exams.
-
Obtain information (exam number, exam date, course number and section number) about exams taking place in building ‘RVR’. Along with that get the room’s capacity and room number.
-
Retrieve information (SSN, first name and last name) about students who are enrolled in both the course ‘CSC133’ and ‘CSC137’. ‘CSC133’ and ‘CSC137’ are both course numbers.
-
Get the information (SSN, first name and last name) about students who take the course ‘Data structure and algorithms’. Also get the section number in which they have enrolled in the course, as well as the instructor of the section.
Section 4
Specify the statements to drop all tables. Pay attention to the order of the drop statements in order to drop everything successfully.
Submission
Instruction about SQL script:
Instead of typing each individual SQL statement in the terminal, you may put all SQL statements into a file. For example, you may put the statements into script my.sql. Then you can run your script by typing “source my.sql” in the terminal. All statements in my.sql script will be executed. The screenshots below show a script my.sql and how to run the script.
Figure 3: the content of my.sql script
In this assignment, you are required to put the statements into specific SQL scripts and then submit the following files to SacCT.
-
Create table statements (file name must be: 1_create_table.sql)
-
Insert statements to populate database (file name must be: 2_populate_db.sql)
-
Queries (file name must be: 3_query.sql). Use “- -“ or “/* */” as comments. List query number before the query. For example:
/*Query 1*/
SELECT … FROM …
-
Statements to drop all tables (file name must be: 4_drop_all.sql)
-
An output file showing query results (file name must be: 5_output.pdf, or
5_output.doc, or 5_output.txt). In this file, for each query, you must:
-
-
Include the number and query description (e.g. Query 1 Retrieve information (SSN…)
-
-
-
Your SQL query (e.g. SELECT …FROM…)
-
The result of running this query. (A screenshot is preferred.)
-
-
Zip file including item 1-5.
