SOLVED COM S 461: ASSIGNMENT 1

Questions
1. (40 points) Consider the following set of requirements for a university database that is
used to keep track of students’ transcripts.
a) For each student, the university maintains student name, student number, social
security number, current address and phone, permanent address, birth date, gender, class
(freshman, sophomore, …, graduate), major department, minor department (if any) and
current degree program (B.A., B.S., …, Ph.D.). Some user applications need to refer to the
city, state, and zip code of the student's current address, and to the student's last name.
Both social security number and student number have unique values for each student.
Each student must have at most one major department. Each student can have at most one
minor department.
b) Each department is described by a name, department code, office phone, and college.
Both name and code have unique values for each department.
c) Each course has a course name, description, course number, credit hours, level, and
offering department. The value of course number is unique for each course.
d) Each section is associated with an instructor, semester, year, course, and section number.
The section number distinguishes different sections of the same course that are taught
during the same semester/year. The possible values for the section number are 1, 2, 3, …
up to the number of sections taught during each semester.
e) A grade report has a student name, section, letter grade, and numeric grade (0, 1, 2, 3, 4
for F, D, C, B, A, respectively).
Draw an ER diagram that captures all the above requirements. Specify key attribute(s) of each
entity set. For each relationship set, specify structural constraints and participation constraints.
Write down any other assumptions that are not given, but are used by you to make the ER diagram
complete.
2. (20 points): Given the ER diagram in Figure 1, create the corresponding relations with constraints
under your account in the Oracle database instance called mydb. Enter at least five records of your
choice into each relation.
a) Put SQL DDL commands to create these relations in a file named createtbl.sql, followed
by the DDL commands to insert at least five rows into each of the relations. Ensure that
when executing createtbl.sql, all relations with constraints and data are correctly created.
b) Put DDL commands to drop the tables you created using createtbl.sql in another file
named droptbl.sql. Ensure that all tables created by createtbl.sql are dropped when
droptbl.sql is executed. DROP TABLE command with cascade constraints is not allowed.
Figure 1: ER diagram of a database to keep track of flight information
3. (20 points): Consider the following relational schemas.
Emp(eid:integer, lname:string, fname:string, age:integer, salary:real)
Dept(dname:char(40), budget:real, managerid:integer)
Managerid is a foreign key to Emp.
Works(eid:integer, dname:char(40), pct-time:integer)
Eid is a foreign key to Emp. Dname is a foreign key to Dept.
An employee can work more than one department; the pct_time field of the Works relation shows
the percentage of time that a given employee works in a given department.
a) Print the last name and age of each employee who works in both the Hardware department
and the Software department.
b) Find the managerid of each manager who manages only the departments with budgets
greater than $1,000,000.
c) Find the last name of each manager who manages the departments with the largest budget.
The IN operator must be used in the query.
d) If a manager manages one or more department, he or she controls the sum of all the
budgets for those departments. Find the managerid of managers who controls more than
$5,000,000.
4. (20 points): Consider the following schemas.
Movie(title, year, length, inColor, studioName, producer#)
StarsIn(movieTitle, movieYear, starName)
MovieStar(name, address, gender, birthdate)
MovieExec(name, address, cert#, netWorth)
Studio(name, address, presC#)
Write SQL scripts to answer the following queries:
a) Who were the male stars in Terms of Endearment?
b) Which stars appeared in movies produced by MGM in 1995?
c) Which movie are longer than Gone With the Wind?
d) Which executives are worth more than Merv Griffin?
Grading Considerations
 Email your homework to TA and cc the instructor. The late policy as indicated in the course
web site will be applied accordingly.
 The homework must be done individually. You may discuss with your classmates about the
problems, but the solutions must be individual.
 Scores will be deducted if the indicated requirements or constraints specified by each question
are not provided in your answers.
 Scores will be deducted if submitted scripts cannot be executed successfully from SQL*Plus.