2007 Fall CS157A Study Guide of First Midterm
Date: Tuesday Sept. 18,2007
Understand the problems of file based systems and what the DBMS is intended to solve.
· Name and briefly describe the function of each major component of a database.
· ANSI/SPARC Architecture 3 levels.
· Explain the features and functions of one of the DBMS ‘s subsystems
1. Understand the meaning of all of the E-R symbols and EER.
The Entity-Relationship Model and EER-diagram
· Entity-relationship model and E/R diagrams
o Entity sets, relationship sets, attributes, keys, roles
o Multiplicity of relationship sets (one-to-one, one-to-many, etc.)
o Weak entity sets
· Relate, or distinguish between, the following terms: foreign key and primary key. Give an example that relates these terms.
· Relate, or distinguish among, the following terms: entity, attribute, identifier, and relationship
· List and give examples of the three types of relationships.
· Distinguish between the maximum and minimum cardinality of entity relationships
· Relate, or distinguish between, the terms: weak entity, strong entity.
· Subclasses ("isa" relationships)
2. Understand the use of foreign key in implementing a relationship
3. Know the basis of the mathematical relation and the properties of a relation.
4. Know the characteristics of superkey, candidate key, primary key, and foreign key.
5. Know the rules of relational integrity and referential integrity.
Sample Problems.
1. What is logical data independence and why is it important?
2. Explain the difference between logical and physical data independence.
3. Define the following terms: relation schema, relational database schema, domain, relation instance, relation cardinality and relation degree.
4. Describe the main components in a DBMS.
5. Explain the following terms: attribute, domain, entity, one-to-many relationship, many-to-many relationship, weak entity set, three-level architecture.
6. Describe the basic concepts of the ER model. Present the diagrammatic representation of these concepts.
- Discuss the advantages and disadvantages of database management systems.
- A company database needs to store information about employees (identified by ssn, with salary and phone as attributes); departments (identified by dno, with dname and budget as attributes); and children of employees ( with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.
Draw an ER diagram that captures this information.
9. Design an ER-diagram for a university database that is used to keep track of students’ transcripts. Specify key attributes for each entity type and indicate the ( 1:1, 1:m, M:N ) in relations.
a. The university keeps track of each student’s name, student number, social security number, current address and phone, birthday, sex, class (freshman, …, graduate), major department, minor department, and degree program (B.A., B.Sc., …., Ph. D.).
b. Each department is described by a name, department code, office number, office phone, and college.
c. Each course has a course name, description, code number, number of units, level, and offering department.
d. Each section has an instructor, semester, year, course, and section number.
e. A grade report has a student, section and grade.
10. Find all the candidate keys of the following relation R(A,B,C,D)
R (A, B,C, D)
1 1 3 4
2 3 2 2
1 1 3 1
2 4 1 4
11. The goal of the three schema database architecture is to
a) Divide the physical database into 3 components
b) Separate the user applications from the physical database
c) Aid the database designer in designing the conceptual database
d) All of the above
12. Which level in the 3 schema database architecture describes the part of the database that is of interest to a particular user group?
a) Internal level
b) External level
c) Conceptual level
d) None of the above
13. Consider the following ER diagram for the next four questions.
(1). Which of the following statements is true?
a) A Customer does not have to participate in the Buys relationship
b) One Vehicle can be considered both a Car and a Truck
c) A Customer buys a vehicle from one Salesperson
d) A Customer is identified by the combination of Street, City and State
(2). Which of the following statements is true?
a) A Customer can only buy vehicles from one salesperson
b) A Salesperson can sell vehicles to only one Customer
c) A given vehicle is sold by one Salesperson to one Customer
d) All of the above
(3). Which of the following statements is true?
a) A Car is identified by the VID
b) A Customer is identified by SSN
c) Both (a) and (b)
d) A Truck and Cycle have no attributes in common
(4). Which of the following statements is true?
a) A Vehicle must belong to one of the subclasses: Car, Truck or Cycle
b) A Car is identified by the Maxspeed
c) A Customer can only buy one Vehicle on any given Date
d) None of the above
14. Which of the following are informal design guidelines for relational schema?
a) Reduce the redundant values in tuples
b) Reduce the null values in tuples
c) Disallow the potential for generating spurious tuples
d) All of the above
15. Explain the distinctions among the terms primary key, candidate key and superkey.