|
|
Database Mid Term Exam
|
Prof Devi
|
Each Question is worth Exactly 10 % each.
1. Define Boyce-Codd Normalization form(BCNF).
2. Define a key,superkey, candidate key, alternate key, foreign key.
3. Using BCNF, Normalize the following attributes(defined on the
board).):
a)
(SS#,LastName,FirstName,StudentIdCourseNo,Semester,Year,Prof,CourseDept,ProfPhone).
b) (VIN,Licience,Address,Name,Registration,Mileage,NumberOfCars)
c) -look on the board
4. What operations are defined for a relational algebra to make it
relationally complete? Write the relations S,P and the relational
schema that S+P are based on. Give one example of each of the operators
defined for relational completeness using the relations S,P.
5. Write the relational algebra, relational calculus, and SQL for
getting all the supplier names that supply jobs in the job city 'Paris'.
6. Write the relational algebra, relational calculus, and SQL for
getting the job numbers for jobs using at least one part available
from supplier S1.
7. Write the SQL for the following statements: 1)Creation of the S
table(+primary key) 2) inserting a single row value into the S table
3) drop the table S 4) Modifying all cities 'Peking' to
'Beijing' 5) copying all rows from table S to another table S2 with the
same relational schema.
8. Find out all supplier names that produce all the same parts as
that of 'Smith'. Write the solution using Relational Calculus.
Convert the relation to SQL.
9. Find out all supplier names that produce no parts that smith
produces'. Write the solution using Relational Calculus. Convert
the relation to SQL.
|
10. Imagine that you
are the chair of a certain department. You need to assign classes to
professors for some semester. You would also like to keep track the
books that each professor uses in his class. Professors sometimes
assign many books for a single class. Since there are so many professors,
you need to keep track of their phone number+address. On some
occasions, two or more professors. Draw the E-R diagram for you
analysis. Using that analysis, write the SQL statements for creating
the tables.
Sample Exam #2:
|
|
Database Final
Exam
|
Prof Devi
|
Each Question is worth Exactly 10 % each. You may select
any 9 of the first 11 questions, and YOU MUST DO #12
1. Define 1NF,2NF,3NF, and 4NF.
2. Define Boyce-Codd Normalization form(BCNF).
3. Define a key,superkey, candidate key, alternate key, foreign
key.
4. What operations are defined for a relational algebra to make
it relationally complete? Give one example of each of the
operators defined for relational completeness using the relations S,P.
5. Write the SQL statements for the following:
- Insert a single
row ('S8','Brown','Atlanta')
into the S table.
- Remove all rows in
the S table which have the city set as 'London'
- Change the City 'Atlanta' to 'Boston' for the supplier 'S8'.
6. Write the SQL statements for creating the following two
tables, creating the primary keys, and foreign keys: Person
(PersonID(PK) - integer , CarID(FK) integer, Name
varchar(30)). Car(CarID (PK) - integer,
PersonID(FK) -integer, Name varchar(30) ). The Person
table has a foreign key CarID while the Car
table has a foreign key of PersonID.
7. Write the relational algebra, relational calculus, and
SQL for getting all the supplier SNO's who are in the city of 'London'.
8. Write the relational algebra, relational calculus, and SQL
for getting all the jobs JNO's who are in the city of 'New York'.
9. Write the relational algebra, relational calculus, and
SQL for getting all the PNO's that are supplied by Suppliers in 'London' that supply parts to jobs in 'New York'.
10. Find out all supplier names that produce no parts that
'Smith' produces. Write the solution using Relational
Calculus. Convert the relation to SQL.
11. Find out all parts that are produced by 'Smith' that are not
produced by 'Mason' that produce all the same parts as smith
produces. Write the solution using Relational Calculus.
Convert the relation to SQL.
12. You would like to implement a database for a large
library. The library has to keep track of hundreds of books, the
Patrons that borrow books from the library, and the fines that a patron
gets for returning late books. Inside the library, patrons search
your database for books on such things as Title, Author, Description, and
ISBN. If a patron asks why he is being fined, your database should
contain all the information necessary for explaining in detail the
cause. When a patron does not return a book for more than two
weeks, a letter must be sent to his home address to remind him of that
book Draw an ER diagram for your analysis. You may need to
explain in point form of some attributes. Write the scheme for each
of the different relations.
|
Lecture Summary
The following sequence of lectures were given in previous semesters.
But, depending on the level of students in the class, I may change the
order of the material to emphasize important materials.
Lecture #1
- Introduction to
Database
- Definitions
- ANSI/SPARC
Architecture
- Database System
Models
Lecture #2
Lecture #3
- Dynamic Multilevel
Indexing with B-Trees
- B+ Trees
- Relational Model
- First Normal Form
- Superkeys, Keys,
Candidate Keys
Lecture #4
- Primary Key, Foreign
keys
- Entity/Referential
Integrity Constraints
- Relational Algebra
- Selection,
Projection, Cartesian Product, Difference,Union
Lecture #5
- Relational Algebra
Transformation Rules
- Selection,
Projection, Join, Outer Join
- Aggregate Functions
and Grouping
Lecture #6
- Tuple Calculus
- Existential and
Universal Quantifiers
- First Order
Predicate Logic
- Codd's Reduction
Algorithm
Lecture #7
- Introduction to SQL
- create, select,
insert, delete, update, drop
- SQL Statements
- Review for Material
for Exam
Lecture #8
- Closed book Mid-Term
Exam
- The exam consists of
twelve questions, of which you may select any ten. Each question
will be equally worth ten percent of your midterm grade.
Lecture #9
- Entity Relational
Modeling
- Database Design
- Client Interview
Process
Lecture #10
- Functional
Dependency
- Normalization
Forms (2,3)
- Boyce-Codd
Normalization Form
- Database Design
Lecture #11
Lecture #12
- Topics to be
determined by class
Lecture #13
- Topics to be
determined by class
Lecture #14
- Topics to be
determined by class
Lecture #15)
- Topics to be
determined by class
Final Exam
- Closed Book Exam
- Cumulative Exam
- Select ten
Questions out of a possible 12
copyrightŠ2001 Prof Devi -
mdevi@comine.com, mdevi@liu.edu. All rights reserved
|
|