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.

Explore the three level architecture of dbms—external level, conceptual level, internal level—and how external conceptual mapping and conceptual internal mapping achieve logical and physical data independence.

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.

Explore the basic constructs of the model—entity, entity set, and relationships—and see how attributes map to columns in the relational model.

Explore the classification of attributes in dbms, including simple vs composite, single value vs multi value, and stored versus derived attributes, with student examples.

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 the degree of relationship in database design, showing how the number of connected entities defines binary and ternary relationships and a single entity connected to itself.

Identify entities from requirements, determine relationships and key attributes, collect nouns, and draw the ER model in a five-step process using a university management system example.

Identify relationships among entities in ER modeling (step two), such as department, teacher, student, and courses, and classify them as one-to-many, one-to-one, or many-to-many.

Identify key attributes for entities in a university management system, then identify other relevant attributes, and finally draw the ER model.

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 total participation and partial participation in entity relationships, illustrated by customers and loans, where loans require a customer while customers may exist without 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.

Map each entity set to a relational table by converting diagrams. Assign attributes as columns and designate the key attribute as the primary key.

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.

Explore the objectives of normalization, establishing a formal framework to verify a design database's accuracy and enable insert, update, and delete without anomalies through the five normal forms.

Explore functional dependence in database relations by identifying determinant attributes and verifying that each value of X maps to a unique Y, with primary keys determining all non-key attributes.

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 a computer center dataset to illustrate normalization, linking courses, teachers, students, and systems; learn first, second, and third normal forms and dependencies in a practical case study.

Define first normal form as every cell containing at most a single atomic value and remove repeating groups by flattening the table, yielding a fully normalized relation.

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.

Explore second normal form by examining fully functional dependencies, partial dependencies, and the shift from first normal form, with normalization and decomposition to remove anomalies.

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.

Identify insert, update, and delete anomalies in the student system table after normalizing first normal form into three tables for second normal form, with code, name, and hourly rate data.

Examine insert, update, and delete anomalies in a student system table under second normal form, and learn how decomposing into three tables resolves these issues toward third normal form.

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.

Explore bc nf, decomposing tables to eliminate insert, update, and delete anomalies. See how determinants become candidate keys to ensure robust normalization.

Explore multivalue dependencies and the fourth normal form. Learn how to decompose a table into course–student and course–book tables to remove multivalue dependencies and enable robust dbms design.

Understand the concept of a transaction as a unit of work that must execute all together, either zero or one hundred percent, to maintain data consistency in money transfers.

Explore how a transaction uses input, read, write, and output operations to move blocks from disk to RAM, copy data to a program variable, and update RAM before disk writes.

Understand the ACID properties of a transaction—atomicity, consistency, isolation, and durability—and how they ensure data integrity, correct concurrency, and durable recovery in DBMS.

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.

Explore live sql in a cloud-based Oracle session, sign up for an Oracle account, and run sql commands in the Sequel Worksheet directly in a browser without installing software.