BMIS441- Data Base Management - Fall 2017
Exam II review – Professor Chen
Contents covered from:
Hoffer’s text: chapters 2,3,4 and 6,7 (study yourself)
Oracle text: Chapters 6, 10,11,12,13 (SQL), 10 (PL/SQL-concepts and programming)
* It is required to complete online quizzes for chapter 6 and 7 from the Hoffer’s text.
Part I. Multiple choice - (35@2) 70 points
Part II Essay and Modeling (ER, E-ER, Functional Dependence, and Normalization) -- (4-5 Questions) (60 -70 points)
Part III PL/SQL programming with Oracle (1-2 questions) (10-20 points)
Data and E-R Modeling and Normalization
1. Steps (and process) on Problem Solving for Modeling a Database Project
2. ER (chapter 2, basic concepts, primary key, modeling with ER)
3. Composite attributes – what is it? How to solve that design flaw (if it causes problem)
4. Derived/computed attributes - what is it? How to solve that design flaw (if it causes problem).
5. Multi-valued attributes - what is it? How to solve that design flaw (if it causes problem)
6. E/E-R (chapter 3) – why E/ER?, business rules, modeling with E-ER
7. Logical Design - Given by a problem, create an enterprise view. Based on it, what is the ?NF and transform it into 3NF
8. Definition of normalization (and de-normalization) and transformation of relations and their purposes.
9. Make sure you understand the process of normalization up to 3NF (review 7,8 on chapter 4 and study additional two questions of 9 and 13 on pages 159-161 from the main text)
10. What are major problems (make sure you can describe their flaws on each table) on the following database (two tables) design? How to fix their problems respectively?
EMPLOYEE
e_id / e_name / e_address / dob / date_employed / skill / … / years_employed / age1234 / John smith / 502 Boone Spokane, WA 99258 / 1-1-1988 / 1-1-2005 / programming,
painting,
drawing, / 6 / 22
5678 / Mary Jones / 567 SE Main st, Seattle, WA 98059 / 2-9-1987 / 3-3-2007 / Gardening,
Computer graphics / 4 / 23
… / … / …
CUSTOMER
c_id / c_name / c_address / c_phone / … / sales_person1234 / John smith / 502 Boone Spokane, WA 99258 / (509)123-4567
5678 / Mary Jones / 567 SE Main St, Seattle, WA 98059 / (206)714-5522
3456 / Jerry Walker / 450 3rd Ave, San Jose, CA 95130 / (408)999-1234 / …
Related to SQL and PL/SQL in ORACLE
1. More on SQL – review all Oracle chapters after exam I (and only those mentioned in the ppt files).
2. Sub-queries, Nested queries
3. Sequence – Why: what is the purpose of using it?; How: write an ORACLE SQL command to create a sequence.
4. SQL commands: TO_CHAR(…), COUNT, DISTINCT,
5. Define database view. List two advantages of using database view?
6. Write a SQL to produce a desired output.
7. What is PL/SQL? What are FOUR blocks (reserved words) used in the PL/SQL block structure?
8. Write an output(s) based on a given PL/SQL(s).
9. In general - Study the syntax of Oracle DDL and DML
Prepare the exam earlier and thoroughly for a good return on your investment!