Database Systems – Exam 1
When taking this exam you may use a single card containing
SQL syntax. The card must be written by you. Write your name on this paper and
write your answers on the answer sheets provided.
- (2 pts each) Briefly define each of the following terms:
- relation
- primary key
- foreign key
- DBA (expand acronym and then define)
- (4 pts) What does SQL stand for and why is it important to know?
- (2 pts each) Briefly describe the types of data defined by each of
the following SQL types:
CHAR(10)
VARCHAR(10)
NUMERIC(8,2)
SERIAL
- (2 pts) What is the difference between
x='A%'
and x LIKE 'A%'
in the WHERE clause of a SELECT query?
- Consider the following three relations/tables that represent the
domain of scheduling university classes.
- room=(room_num,capacity,desc)
- schedule=(id,course,inst_id,room_num,time_slot)
- instructor=(inst_id,lastname,firstname,title)
Assume that matching field names represent related fields among the
tables.
Table: room |
room_num |
capacity |
desc |
JSC 341 |
30 |
Main math room |
JSC 342 |
18 |
Math education room |
JSC 343 |
24 |
Computer science lab |
JSC 427 |
32 |
Consumer science room |
JSC 130 |
20 |
Biology Room |
Table: schedule |
id |
course |
inst_id |
room_num |
time_slot |
523 |
CORE 1033 |
534 |
JSC 341 |
A |
524 |
CORE 1033 |
534 |
JSC 341 |
B |
525 |
MATH 1003 |
716 |
JSC 325 |
A |
526 |
MATH 2014 |
398 |
JSC 427 |
D |
Table: instructor |
inst_id |
lastname |
firstname |
title |
534 |
Adams |
Joe |
Assistant Professor |
716 |
Tireless |
Tom |
Instructor |
398 |
Green |
Anne |
Professor |
322 |
Smithe |
Sammy |
Associate Professor |
- (4 pts) Represent the tables graphically using UML notation. Be sure to
specify the cardinality of relationships based on the structure of the
tables and what you know of the problem domain.
- (3 pts each) Given the structure and contents of the tables
above, show the output that would be produced by each of these queries:
SELECT course, capacity FROM room NATURAL INNER JOIN schedule
SELECT course, capacity FROM room FULL OUTER JOIN schedule USING (room_num)
- (3 pts each) Construct SQL statements to achieve the following
results (do not assume that the contents of the tables exactly
match the samples given above):
- Create the room table having field names and types as
illustrated above. Also specify the primary key.
- Add an Associate Professor to the instructor table
whose name is Mary Evans. Assign an id number of 598.
- Remove all classes scheduled in rooms that house fewer than
20 students.
- Assign all sections of “CSCI 1303” to be taught in
“JB 105”
- List all course names and id numbers for whom no instructor
has been assigned.
- List all course names that are taught by instructors who have
the word “Professor” in their title.
- List the number of courses taught at each time slot. Order
the list by time slot.
- List all rooms that have more than 5 courses taught in them.
- Create a view named all that lists all information for
all classes including the full room information and the full
instructor information. Have the courses ordered first by the
course name then by the time slot.
- Use the view you created in problem 5(c)ix to produce a
list of rooms (along with their descriptions) that are in use
during time slot B.
- With a single query modify the capacity of all rooms whose capacity
is less than 25 to have a capacity of 25 (i.e., make sure every room has
a capacity of at least 25).
- Remove all rows from the instructor table.
- Suppose we have a database that lists all registered voters for Taylor
county. The database is used to identify which polling station a voter is
allowed to use and whether or not they have voted yet. The following
information is to be kept. The SiteID and the SiteName
represent the polling station. The SiteAdmins field identifies who
the contact person for that particular site is along with their contact
information. The VoterID, VoterName, and Voted fields
provide information regarding a given voter.
SiteID |
SiteName |
SiteAdmins |
VoterID |
VoterName |
Voted |
145 |
VFW Building |
Sam Jones (123-4567) |
32657 |
Alice Aitken |
yes |
145 |
VFW Building |
Sam Jones (123-4567) |
34567 |
Bea Bacon |
no |
145 |
VFW Building |
Sam Jones (123-4567) |
56563 |
Clyde Caldwell |
yes |
145 |
VFW Building |
Sam Jones (123-4567) |
34342 |
Drake Duncan |
no |
145 |
VFW Building |
Sam Jones (123-4567) |
09238 |
Elias Egbert |
no |
476 |
First Church |
Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) |
12342 |
Fred Flintstone |
no |
476 |
First Church |
Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) |
23244 |
Greg Grigson |
no |
476 |
First Church |
Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) |
34454 |
Heidi Hill |
no |
476 |
First Church |
Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) |
45454 |
Ida Ingels |
yes |
476 |
First Church |
Amy Hu (234-5678 or 234-6789); Jan Evans (456-7890) |
58688 |
Jeff Jordan |
yes |
567 |
United |
Mike Miller (679-2322); Kevin Klein
(235-3433 or 236-2342) |
23424 |
Lionel Liu |
yes |
567 |
United |
Mike Miller (679-2322); Kevin Klein (235-3433 or 236-2342) |
93483 |
Nathan Nix |
no |
567 |
United |
Mike Miller (679-2322); Kevin Klein (235-3433 or 236-2342) |
89343 |
Oprah Oliver |
no |
- (6 pts) Name as many reasons as you can think of that this table
is not in BCNF.
- (2 pts) Based on your answer to question 6a, how
would you classify the form of this table (unnormalized, 1NF, or BCNF)?
- (4 pts) List the functional dependencies that exist in this
table.
- (8 pts) Convert the table to BCNF. For each resulting table
identify the primary key field(s). Show the table structure using
list notation only (i.e., do not list the data in the table for
your answer).