Simplified Approach to DBMS: The Complete Package

Explore the three level architecture of dbms external, conceptual, and internal, and how data independence and multiple external views are achieved.

Map the external, conceptual, and internal views of a database using external-conceptual and conceptual-internal mappings. Understand how schemas define data at each level and how field concatenation forms external attributes.

Explore hierarchical data modeling, the radical data model, and how to convert a relational design into a hierarchical structure using suppliers, parts, and shipments as examples.

Explore the hierarchical data model's insert, update, delete, and retrieve operations, exposing anomalies like parent-child constraints, cascading deletions, and asymmetric retrieval.

Learn the network data model, a graph-based approach that handles many-to-many relationships and nonhierarchical connections, and see how relational data on suppliers, parts, and shipments becomes a connected network.

Explore the insert operation on the network data model, showing how supplier, part, and shipment nodes—together with quantities—are created and linked, including inserting suppliers without parts and adding shipments.

Explore how the network data model handles update operations by changing supplier, shipment, and part details with a single update, and avoid update anomalies seen in ablate operations.

Explore the network data model's delete operation, showing how removing a part and its shipments updates supplier connections without affecting suppliers, avoiding the lead operation anomaly.

Explore retrieval operations in the network data model by traversing suppliers, shipments, and parts to display related numbers. It highlights symmetric query logic and complexity issues.

Explore the relational data model, its table-based structure and how insert operations use primary and foreign keys to link supplier, part, and shipment data while avoiding anomalies.

Learn how update operations work in the relational data model, where a primary key ensures single updates for changes to supplier, part, or shipment, avoiding anomalies.

Learn how the relational data model handles delete operations: deleting a supplier removes related shipments but leaves parts intact, while deleting a part clears shipments without affecting suppliers.

Explore how the relational data model handles retrieve operations with simple, symmetric queries that map parts to suppliers, demonstrating no anomalies and overall simplicity.

Learn how the entity-relationship model visualizes software requirements by identifying entities and relationships, bridging information gaps from requirements to design with a top-down approach.

Learn the symbols of the ER model, including entity sets, relationships, and attributes, with emphasis on key, composite, multi-value, and derived attributes, and how visual representations convey database structure.

Explore strong and weak entity sets, and learn how a discriminator combines with a strong key to form an identifying relationship, as in customer and loan.

Explore strong and weak entities using a loan payment history, showing how an identifying relationship and a discriminator distinguish payments across loans.

Explore cardinality and connectivity in relationships, including minimum and maximum cardinalities for one-to-one, one-to-many, many-to-one, and many-to-many mappings.

Explore cardinality and participation in database relationships, using supplier–part and employee–department examples to illustrate one-to-one and many-to-one connectivity, and total and partial participation.

Learn to convert diagrams to tables by flattening composite attributes into component columns, ignoring the root, and using the concatenate operator to reconstruct full names and addresses.

Store multi-value attributes in a separate table with two columns: the entity primary key and the multi-value attribute; enforce a foreign key to the master table to support multiple values.

Learn to handle derived attributes when converting an entity set to a relational model, such as age from date of birth, and ignore them in design.

Explain handling of many to many relationships by converting diagrams to tables, and creating a relationship table with primary keys from each entity, plus foreign keys and a composite key.

Map a many-to-one relationship between student and college into two tables, linking them by copying the college primary key into the student table as a foreign key.

Explore converting one-to-many relationships into tables by moving the primary key from the one side to the many side. Learn when to create two tables for college and department.

Learn how to convert one-to-one ER relationships to tables by copying primary keys, and apply total versus partial participation to determine table design.

Explore normalization to refine database design by decomposing large tables into smaller normalized tables, apply the five normal forms, and avoid insert, update, and delete anomalies.

This lecture defines fully functional dependence: a Y is fully functionally dependent on a composite X only if Y depends on X and on no proper subset of X.

Explore first normal form and decomposition to split a table into two relations, identify a table identifier, distinguish non repeating and repeating attributes, and define composite primary and foreign keys.

Explore insert, update, and delete anomalies in first normal form, where course code and rule number form the primary key, leading to issues solved by second normal form.

apply the rule of decomposition to reach second normal form by removing partial dependencies and creating separate tables for course details, rule numbers, and total hours.

Learn how third normal form eliminates transitive dependencies and update anomalies by decomposing a table into related tables, using primary keys and foreign keys to preserve data integrity.

Understand the five transaction states—active, partially committed, committed, failed, and aborted—and how transitions and rollback preserve atomicity in a DBMS.

Explore how database transactions handle conflicting and non-conflicting operations on data items, learn when reads and writes on the same item cause unrepeatable reads or lost updates.

Explore sql as a non procedural query language that retrieves data using ddl and dml statements, with Oracle data types like number and character, and practice via sql plus interface.

Design a student table with rule number, name, marks, and date of birth; create the table, then insert, select, update, and delete records using SQL.