Download Functional Dependency - Database Design - Quiz and more Exercises Introduction to Database Management Systems in PDF only on Docsity!
EXAM Date: Thursday October 11, 2007
(1) 20% First Midterm materials. . (2) 80% of the new topics.
Readings : Relational DB, SQL, Functional Dependency
Relational Algebra ----1. the language
a. select b. project c. join d. the set operations union intersection difference e. renaming f. division
- RA queries
- Primary keys and foreign keys
- Integrity rules and enforcement
- Relational algebra
Functional Dependency
Armstrong Axioms,
Functional Dependency Graph
- The exam will be comprehensive. Definition type questions over the terminology (relational algebra terminology as well. Given a set of relations and some sample data for the relations be able to give the relation that results from a relation algebra expression.)
- Given a set of relations and a relational algrebra expression, be able to describe in English what the result is.
- Given a set of relations, be able to produce relational algebra expressions that produce some stated result (similar to your homework).
Test material will be drawn from the text book, lecture, assignments and any
supplementary material provided in class. G o through the reading and the examples
in the textbook. Consider the problems at the end of each chapter and work some of them.
You should review the following:
- all the lecture notes
- all the assigned readings
Sample Problems
- The relational model of database management was proposed in 19__ by Dr. E. F. Codd at IBM Research laboratory in San Jose, California.
A. 60 B. 70 C. 80 D. 90 E. 95 F. 97
- Multiple Choice. When we map a multivalued attribute for entity E from the ER model to the relational model, we will create (a) many relations, one for each of the distinct values of the attribute (b) one relation that contains a foreign key and a column for the attribute (c) a column in the relation that represents the entity E (d) none of the above
- Codd's first rule says that all information in a relational datbase is represented by values in ________.
A. databases B. rows C. columns D. tables
- Which of the following is not a database system?
A. DB2 B. RDB C. INGRES D. SYBASE E. ORACLE F. SQL
- Tuple is synonymous with ________
A. table B. row C. column
- If two data objects, A and B, are relate in a one-to-many relationship a. every A can be related to one more Bs b. every B can be related to one or more As. c. both a and b are true. d. either a or b is true, but not both. none of the above
- Each row in a table describes one occurrence of an ________.
A. value B. file C. entity D. table E. relation
- The system tables, also known as the system catalog, is also known as the _________.
A. database tables
- Entity integrity reqires that no primary key be allowed to have a ______ value.
A. zero B. blank C. negative D. imaginary E. null
- Designers generally start with lists and then move to sketches of the tables and the relationships among them, called data-structure or ______________ diagrams.
A. E-R B. R-E C. Codd D. SE E. Primary-key
17.. A complete database design should include planning for primary key/foreign key consistancy or _______ integrity.
A. association B. referential C. entity D. transactional
18.. Which of the following people invented the relational database model? A. Dr. E.F. Codd B. C.J. Date C. L.T. Data D. Dr. Peter Chan
- Assume you have the database Department (dnumber,ssn,,dname, mgrssn, mgrstartdate. Headquarters, Administration) Employee (ssn, dno) Dept_Location (dnum, dlocation) Project (dnum, pnumber, pname, plocation, Product, Reorganization) Employee (ssn, fname, minit, lname, bdate, address, sex, salary, superssn, dno) Works_On (essn, pno, hours) Project (pnumber) and Works_On (pno) Dependent(essn, dependent_name, bdate, sex, relationship,Spouse)
Present the following Queries in relational algebra and SQL Q1: select all employee SSNs.
Q2: Retrieve the salary of every employee.
Q3: Retrieve the names of all employees who do not have supervisors.
Q4: Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’.
Queries Requiring Table Joins
Q5: Retrieve the SSNs and names of all employees who work on project number 1, 2, or
Q6: Retrieve the name and address of all employees who work for the ‘Research’ department.
Q7: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.
Q8: Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
Q9: Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.
Q10.: Retrieve the total number of employees in the company.
Q11: Retrieve the total number of employees in the ‘Research’ department.
Q12: For each department, retrieve the department name, the number of employees in the department, and their average salary.
Q13: For each project, retrieve the project number, the project name, and the number of employees who work on that project.
Q14: Retrieve all employees whose address in Houston, TX....
Q15: Find all employees who were born during the 1950s.
Queries Requiring Use of Recursive Tables
Q16: For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
20.Design an Entity-Relationship schema for a car service station database. The database should contain the information about:
customers: name, address, phone number; employees: name, SSN; cars: license plate number, make, model, owner (who is a customer); repairs: car repaired, mechanics, date, cost.
Customers are identified by their names and addresses. There is only one repair performed on a given car on a single day. Only the employees that are mechanics can perform repairs. More than one mechanic can do a specific repair. Make any necessary additional assumptions that make sense in the real world.
- You are given the following relational schema:
identifying identity set is eliminated. Translate the following ER diagram in relational model.
- ) List all the candidate keys in this relation: R(A B C D) 1 3 1 3 1 3 3 1 2 2 1 3 1 3 2 1 1 2 1 2
- Multiple Choice. Give the following two tables R and S R(A B C) S(C) a1b1c1 c a1b2c2 c a1b1c3 c a2b2c a2b2c a2b2c a3b3c The result of R ÷ S is (a) A table with column A and B whose one row is (a2,b2) (b) A table with column B whose rows are (b1),(b2) and (b3). (c) A table with column A whose rows are (a1),and (a2). (d) A table with column A whose one row value is (a3). Answer:
- True or False. The rename operator returns an existing relation under a new name. ρA(B) is the relation B with its name changed to A Answer:
- The same relational algebraic expression can be written in many different ways. State which one is true or false.
(a)A ×B ⇔ B × A
(b)A ∩ B ⇔ B ∩ A
(c)A ∪B ⇔ B ∪ A
(d)(A - B) is same as (B - A)
(e)σc1 (σc2(A)) ⇔ σc2 (σc1(A)) ⇔ σc1 ^ c2(A)
31.Find the functional dependency graph of the following table. R(A B C D)
A B C D 1 1 2 1 3 1 2 2 2 2 1 1 1 3 3 2
