Dept of CS Western Illinois University
IS 342 AMARAVADI
TOPICS FOR SECOND MIDTERM
Database Logical Design (Part I) (To the extent covered)
-Objectives of design: most efficient form, no uncontrolled redundancies, querying/reporting facilitated, easy to implement databases.
-Well structured relation: insertion, deletion and update anomalies.
-Methods of design: design using thumb rules and design from normalization theory
-Design using thumb rules for 1:1, 1:M and M:N type relationships.
-Design with normalization
-Design concepts: well structured and ill-structured relations, primary keys, candidate keys, composite key, non-key, foreign and cross-reference keys.
-Design concepts: Functional dependency, FD diagrams, determinants and candidate keys, Full functional dependency rule.
-Rules of functional dependency [to the extent covered]: reflexive, augmentation, union, decomposition, transitivity, substitution.
Logical Database Design -II
-The normalization process:
1. Remove repeating groups 1st NF
2. Remove partial dependencies 2nd NF
3. Remove transitive dependencies 3rd NF
4. Remove multivalued dependencies 4th NF (if discussed)
-More design concepts: repeating groups, partial dependency, transitive dependency and multi-valued functional dependency.
-The normalization process with the FD approach:
1. Draw an FD chart
2. Group FDs together based on common determinants
3. Place each FD in a separate table
4. Add foreign keys if necessary.
-The normal forms: 1st, 2nd, 3rd, 4th and BCNF (if discussed)
SQl: A Standard for Database Processing
-Codd’s rules for RDBMS: information representation, guaranteed access, dynamic on-line catalog, comprehensive data sub-language, view updating, high-level insert, update and delete, physical, logical, integrity and distribution independence, non-subversion.
-Types of SQL: DDL, DML, SQL/T, SQL/AU (DCL), SQL/I
-DDL: Create/alter/drop table, Create/drop index, create/drop view. [need to be able to write DDL & DML statements]
-DML: Insert, update, delete, select.
-Types of Functions (and examples): Logical, Arithmetic, String and Date.
-Select statements using functions, expressions, logical operators, aggregates, group by and having.
Additional notes
Since you need to be able to draw ER diagrams, it is suggested that you review the guidelines from “Database Analysis” Chapter.
SAMPLE TEST
SECTION I
1. CODD’s rule of “Guaranteed Access,” states that it is necessary to have the primary key value to access records.
TrueFalse
2. If we have a functional dependency a c this means that ‘a’ can serve as the candidate key.
TrueFalse
3. Partial dependencies occur between non-key attributes.
TrueFalse
4.Create Index is an example of a DML command
TrueFalse
5.A well-structured relation
- Is one that has repeating groups
- Is one that does not have unwanted functional dependencies
- Is one that has anomalies
- Is one that has transitive dependencies
SECTION II
A Library is planning to implement a database. An initial version of the database was developed rather poorly as shown below. Based only on the sample data,
BOOKS ORDERED
ISBN#
/ AUTHOR / TITLE / DEPT. / PRICE / PUBLISHER0471556688 / Claybrook / OLTP Systems / ISDS / $20.00 / John Wiley
0139281282 / Krishnamurthy / TP Systems / CS / $10.95 / Prentice Hall
0139281282 / Murthy / TP Systems / CS / $10.95 / Prentice Hall
0136570089 / Highleyman / TP Performance / ISDS / $50.00 / Prentice Hall
0131011391 / McNurlin / IS Management / Mgmt. / $130.00 / Praeger
0131011391 / Sprague / IS Management / Mgmt. / $130.00 / Praeger
0970035209 / Travis / Scorecard System / Mgmt. / $14.95 / Resource Pub.
1)Draw a functional dependency diagram
2)Normalize the “books ordered” table and explain your rationale.
3) Based on normalized table, write an SQL command to reduce the price of all Prentice Hall books by 20%. Use your redesigned table for the SQL command.
4)Write an SQL command to create a view that shows books ordered as seen in the table above.