316 Review (After the Mid-Term)
316 Review (After the Mid-term)
Oracle SQL Plus
Oracle Developer
Chapter 6. Normalization
Chapter 7.Methodology- Conceptual Database Design
Chapter 8.Methodology- Logical Database Design
Chapter 9.Methodology- Physical Database Design
Oracle SQL Plus
Create Table
Define validation rules
Define relationships between tables
Referential Integrity
Inserting records (values) into tables
SQL commands Assignment
Oracle Developer
Form Builder
Query Builder
Report Builder
Chapter 6. Normalization
Data redundancy
Anomalies
Insertion Anomaly
Deletion anomaly
Update Anomaly
Functional Dependencies
Process of Normalization
1 NF: A table that contains no repeating groups
2NF: 1NF + Every non-key attribute is fully functionally depending on the primary key
3NF: 2NF + The only determinant in the relation is the candidate key
Design methodology:
Conceptual
Logical
Physical
Chapter 7.Methodology- Conceptual Database Design
Steps:
Entity types
Relationship types
1-1, 1-M, M-M
Attribute Domains
Candidate keys, primary keys, Alternate keys
E-R Diagram
Review Local conceptual model
Document each step
Simple/Composite attributes
Derived attributes
Document attributes
ERD examples
Chapter 8.Methodology- Logical Database Design
Starting point: Conceptual ERD
Steps:
Map local conceptual data model to local logical data model
Remove
M:M relations
Recursive relations
Multi-valued attributes
Reexamine 1-1 relationship
Remove redundant relationships
Derive relations from local logical data model
Strong entities
Weak entities
Validate relational model using normalization
Validate model against user transactions
Transaction Issues: Insert, Delete
Draw ERD
Define integrity constraints
Required data
Attribute domain constraints
Entity integrity
Referential Integrity
Enterprise constraints
Handling Integrity Constraints
Inserting occurrence into child relation
Delete occurrence into child relation
Update foreign key of child occurrence
Insert occurrence into parent relation
Delete occurrence from parent relation
No action
Delete referenced child occurrence
Set Null
Set default
Build and validate global logical data model
Merge entities from local views
Check for future growth
Draw final ERD
Chapter 9.Methodology- Physical Database Design
Starting point: Global logical data model
Translate global logical data model for target DBMS
Design base relations for target DBMS
Database Design Language
Design enterprise constraints for target DBMS
SQL
Design physical representation
Analyze transactions
Tuning the Performance
Choosing indexes
SQL
Situations for Denormalizations
Design security mechanisms
Design user views
Design access rules
Document
Monitor and tune the Operational system