REPORT3-ADVDB.pdf

SCHEMA DESIGN NORMALIZATION & SCHEMA DESIGN >> Database schema design is the process of defining a structure that represents the relationships, constraints, and organization of data within a database.  2 TYPES OF DATABASE SCHEMA Logical Schema 01 Represents the logical...

SCHEMA DESIGN NORMALIZATION & SCHEMA DESIGN >> Database schema design is the process of defining a structure that represents the relationships, constraints, and organization of data within a database.  2 TYPES OF DATABASE SCHEMA Logical Schema 01 Represents the logical design of the database without worrying about how data is stored physically. Physical Schema 02 Deals with the storage of data on the hardware and aims at optimizing performance. STEPS TO CREATE A GOOD SCHEMA DESIGN 01 Requirement Analysis and Data Modeling 02 Normalization of Data 03 Define Primary Keys and Foreign Keys 04 Choose Appropriate Data Types 05 Indexing and Performance Optimization NORMALIZATION is a database design technique that reduces data redundancy and improves data integrity. advantages of normalization : -> Ensures consistency across the database. -> Reduces space by eliminating redundant data. -> Simplifies query structure, making it faster to retrieve data.  remove remove multivalued remove multivalued attributes transitive dependencies dependencies 0NF 1NF 2NF 3NF BCNF 4NF 5NF remove remove partial remove remaining remaining dependencies anomalies resulting anomalies from duplicated candidate keys 0NF. Table with multivalued attributes, not in 1st Normal Form → 1st Normal Form. Table with no multivalued attributes and unique rows, in 1st normal form →  2nd Normal Form. Removed partial dependencies Student: Registration: →  3rd Normal Form. Removed transitive dependencies Student: Faculty: →  Further Normalization BCNF 4NF 5NF based on keys and FDs based on keys, multi- based on keys, join of a relation schema valued dependencies dependencies RESOURCES PAGE