324: Review I for Database Management

Concepts:

• Databases
• DBMS
• Logical Data Models
• Hierarchical Models
• Network Models
• Relational Models
• Primary key
• Foreign Key
• Referential Integrity Rules
• Entities
• Relationships
• Mapping Cardinality
• Optionality
• Attributes
• Composite Attributes
• Multi-valued Attributes
• Relations
• Relational Models
• Recursive Relationships
• Weak Entities
• Super and Sub Entities
• Gerund
• SQL
• Constraints 
• Oracle Meta Tables
• Oracle Data Types

Skills and Techniques:

• Conceptual Data Modeling (Entity-Relationship Diagramming) from the understanding of business objects and their relationships or from forms, reports, tables, and other data carrier to understand data requirements. 
• Transform conceptual data models into normalized tables (relational models) manually by following the rules
• Model business data using advanced constructs, including super-sub types, exclusive relationships, recursive relationships, weak entities, and gerunds
• Implement business data models involving advanced constructs into relational models
• Write SQL statements to implement relational models with constraints for primary keys, foreign keys, domain validation rules, and business rules
• Create and management Oracle database objects such as tables, users, sequences, database links, synonyms, constraints, and views

Sample Problems:

Instruction: This list shows the types of questions you may see on the exam. Studying them will greatly improve your chance of success. However, it by no means indicates the actual content of exam questions.

1. What is a logical data model? Give three examples
2. Give an example of relational model.
3. What is a primary key? What is a foreign key?
4. How does the relational model allow you to track related records? What about hierarchical models?
5. How many foreign keys can a table have? How many primary keys can a table have?
6. Can a primary key have a duplicate values or missing values?
7. Can a foreign key have a duplicate values or missing values? 
8. What is the mapping cardinality between dorms and rooms?
9. Synonyms for tables, rows, and columns?
10. How do you delete a table even though other tables reference it?
11. How do you look up all the tables you have?
12. How to you look up all the constraints you have?
13. If you know a table name, how can you find its structure?
14. If you know a table name, how can you find its constraints defined on it?
15. What is an Oracle dictionary table?
16. What is special with weak entities?  When do you use weak entities?
17. How do you handle the dependents of employees for an insurance database?
18. When do you use associative entities or gerunds?
19. When do you use recursive relationships?
20. How do you handle two types of patients: out patients and in patients, in a hospital database?
21. What are the advantages of using super and sub type entities?
22. What are exclusive relationships?  Give an example
23. Why should a recursive relationship always be O:O?
24. What is a multi-valued attribute and how do you handle it?
25. Compare the similarities and differences between a gerund and a weak entity set
26. What are the differences between a relational model and an entity-relationship model? Is primary UID the same as a primary key?
27. What is different between mapping cardinality and optionality?

Practice Problems:

1. The Sam’s Store orders supplies from various vendors. Internally, as a convention of organizing inventories, supplies are organized into categories and groups, and the store assign employees to manage products in specific categories. Each manager manages employees in specific groups. Please create an ER Diagram to model the related business objects.
2. Create an ERD using Oracle Data Modeler for a health club, which offer memberships to both individual and local business members. Business members have data such as the number of employees and company name while individual ones have last name, and first name. All memberships have definite valid period, and after the expiration, each member can renew and obtain a new membership. Club employees are sometimes assigned to manage business members, and they help renewals of business memberships. Member will be issued access key cards for the members to access the club.  Key cards can be enabled or disabled according to membership validity and whether a card is reported lost.
3. When members join the health club, they pay a fee for a certain length of time. Create an ER diagram to model the related business objects such as Members, Memberships, Renewals, and Employees. Note that, for setting reasonable renewal prices, the system must keep track all past renewal prices, and each time a member comes into the club. The system will identify heavy users and generate a report so the manger can ask them to renew their memberships early at a reduced rate. Likewise, the system should identify those who do not come to the club often so that the manager can call them and attempt to attract them in the club.
4. The following is a relational model. Use Data Modeler to draw the ERD that produces the relational model and write SQL statements to Implement the following relational model in Oracle. Note the italics are primary keys and you will need to identify foreign keys.

   Members (ID, Lname, Fname, Phone, Address, State, City, Zip, Student, Professor)
   Students (SID, Major, AdmissionDate, Faculty_Advisor)
   Professors (IID, Department, Area)
   Memberships (Member, Number, ExpirationDate)
   Events (Name, Time, cost, place, Followup_Event, Followup_Time)
   Signups (member, Event_Name, time)

5. Transform the following ERD into a relational model.