Fundamentals of Database Systems - Lecture Notes Review (DBMS, ER, Normalization, Keys)

Flashcards covering DBMS concepts from the notes (DBMS basics, data models, architecture, ER models, normalization, keys, relational algebra, and ER-to-relational mapping).

What are the two main parts of a Database System?

1) Database (where data is stored) and 2) Database Management System (DBMS) which helps manage and use the database.

In the online library analogy, what does the Database represent?

A digital library where books' information (titles, authors, genres, availability) is stored.

In the online library analogy, what does the DBMS represent?

A librarian that helps you find, borrow, return, and manage books efficiently.

Name the three basic DBMS operations described in the notes.

Storage (store data), Modification (update data), Extraction (retrieve data).

What are the levels of Abstraction in DBMS?

Physical Level, Logical Level, and View Level.

What do data abstraction and data independence refer to in DBMS?

Hiding the details of data storage and organization from users; changes in storage do not affect how users interact with data.

List the main data models discussed in the notes.

Hierarchical Model, Network Model, Relational Model, Entity-Relationship (ER) Model, Object-Oriented Model.

What is a Data Model?

A framework for organizing and structuring data, defining how data is connected, stored, and processed.

What is the Relational Model?

Data organized into tables (relations) with rows (tuples) and columns (attributes).

What is the ER Model?

Uses entities and relationships, often visualized with ER diagrams.

What is the Object-Oriented Model in DBMS?

Stores data as objects, integrating object-oriented programming concepts with databases.

What are the main purposes of data models?

Design, Documentation, Efficiency, and Integrity of data management.

What is database architecture?

The overall structure and organization of a database system, including components, their relationships, and design/operational principles.

Name the six components of Database Architecture.

Data Storage, Data Access, Data Management, Security, Scalability, Flexibility.

Describe Single-Tier Architecture with an example.

Application and database reside on the same machine; example: Microsoft Access.

Describe Two-Tier Architecture with an example.

Separate client application and database server; example: client-desktop app querying a central SQL Server.

Describe Three-Tier Architecture with an example.

Presentation (client), Application processing (middle tier), Data management (database server); example: Online Shopping Website.

What are the advantages of Single-Tier, Two-Tier, and Three-Tier architectures?

Single-Tier: simple setup/low cost; Two-Tier: efficient for small to medium apps; Three-Tier: scalable, flexible, secure for large-scale web apps.

What is Relational Algebra and its nature?

A procedural query language that provides a theoretical foundation for relational databases and SQL.

List the basic operations of Relational Algebra.

Selection (σ), Projection (π), Union (U), Set Difference (-), Cartesian Product (×), Rename (ρ).

What does the Selection operation do in Relational Algebra?

σ(condition)(R) returns the subset of R that satisfies the condition.

What does the Projection operation do in Relational Algebra?

π(attributes)(R) returns only the specified attributes from R.

What does Union in Relational Algebra do?

U combines the tuples from two relations with the same schema, removing duplicates.

What does Set Difference do in Relational Algebra?

R1 - R2 returns tuples that are in R1 but not in R2.

What does Cartesian Product do in Relational Algebra?

R1 × R2 pairs every tuple of R1 with every tuple of R2.

What is Rename (ρ) in Relational Algebra used for?

Renames the attributes or relation to new labels.

What is Join (in extended Relational Algebra) used for?

Combines related tuples from two relations based on a common attribute, often followed by a join with another relation.

What is Intersection (∩) in Relational Algebra?

Returns the tuples that appear in both relations.

What is Division (÷) in Relational Algebra used for?

Finds tuples in one relation that are related to all tuples in another relation (e.g., students enrolled in all required courses).

What is an ER Diagram?

A diagram that represents entities, attributes, and relationships to model database structure.

Why are ER Diagrams useful in DBMS design?

They help conceptualize the database, identify necessary fields, reduce complexity, and preview the logical structure.

What shapes/notations are used in ER Diagrams?

Rectangles for entities, ellipses for attributes, diamonds for relationships; lines connect entities/attributes; primary keys are underlined; double ellipses indicate multi-valued attributes.

What is a Weak Entity in ER Diagrams?

An entity that depends on another entity and has no key attribute of its own; represented by a double rectangle.

What is Generalization in ER Modeling?

Bringing several entities together into a generalized entity based on shared characteristics (e.g., Birds).

What is Specialization in ER Modeling?

Dividing a group of entities into sub-groups based on characteristics (e.g., Person into Employee, Customer, etc.).

What is Inheritance in DBMS context?

A mechanism where lower-level entities inherit attributes from higher-level entities; related to generalization/specialization.

Define Entity, Attribute, and Relationship in ER terms.

Entity: a thing of interest; Attribute: property of an entity; Relationship: association between entities.

What is a Key Attribute?

An attribute that uniquely identifies an entity within an entity set; it is underlined.

What is a Composite Attribute?

An attribute composed of multiple attribute components; shown as connected ellipses.

What is a Multivalued Attribute?

An attribute that can have multiple values for a single entity; shown with a double ellipse.

What is a Derived Attribute?

An attribute whose value can be derived from other attributes; shown with a dashed ellipse.

What is a Primary Key and how is it chosen?

A Candidate Key selected by the designer as the main unique identifier; it must be non-null and unique; only one primary key per relation.

What is a Super Key?

A set of attributes that can uniquely identify each tuple; may include extra attributes.

What is a Candidate Key?

A minimal superkey; a minimal set of attributes that can identify each tuple uniquely.

What is an Alternate Key?

Candidate keys not chosen as the primary key.

What is a Foreign Key?

An attribute in a referencing relation that refers to the primary key of a referenced relation; can be NULL; not necessarily unique.

What is a Partial Key?

A key that cannot identify all records by itself but can select a subset of related tuples.

What is a Composite Key?

A primary key that consists of multiple attributes.

What is a Unique Key?

A key that is unique for all records, may be NULL, and is non-updatable in many cases.

What is a Surrogate Key?

An artificial, unique, non-null, updatable key used when no natural key is suitable.

What is a Secondary Key?

A key used for indexing to speed up approximate searches.

What is the first step to finding candidate keys (essential attributes)?

Identify essential attributes: attributes not present on the RHS of any functional dependency.

In the example R(A,B,C,D,E,F) with A→B, C→D, D→E, which attributes are essential?

A, C, and F.

In that same example, which combination forms the candidate key after verifying closure?

CE is the candidate key (the only possible candidate key).

How many candidate keys and how many super keys exist for that CE example?

Only one candidate key (CE); total super keys = 16 (2^4, since there are 4 non-essential attributes).

What is Rule-01 in ER-to-Relational Mapping?

A strong entity set with only simple attributes requires only one table in the relational model.

What is Rule-03 in ER-to-Relational Mapping?

A strong entity set with multi-valued attributes requires two tables (one for simple attributes; another for the multi-valued attributes with the key).

What is Rule-04 in ER-to-Relational Mapping?

Translating a relationship set into a table: the table includes primary keys of participating entities and descriptive attributes.

What is Rule-05 in ER-to-Relational Mapping for binary relationships with m:n cardinality?

Three tables are required: A, R, and B.

What is Rule-06 about in ER-to-Relational Mapping with participation constraints?

If there is total participation, the foreign key becomes NOT NULL (cannot be null).

What is Rule-07 about in ER-to-Relational Mapping for weak entities?

A weak entity set requires two tables in the relational model (the identifying relationship plus the weak entity table).

Why is normalization used in DBMS?

To reduce data redundancy, structure data into related smaller tables, and improve data integrity.

What are the Normal Forms mentioned and their quick definitions?

1NF: atomic values; 2NF: 1NF and all non-key attributes fully functionally dependent on the primary key; 3NF: 2NF and no transitive dependencies; BCNF: stronger form of 3NF; 4NF: no multi-valued dependencies; 5NF: no join dependencies (join is lossless).

What are some advantages of Normalization?

Reduces redundancy, improves organization and data consistency, supports relational integrity, and enables flexible design.

What are some disadvantages of Normalization?

Hard to start design without user needs, higher normal forms can degrade performance, time-consuming for complex decompositions, risk of poor design if misapplied.

What is a Candidate Key vs a Primary Key vs a Super Key?

Candidate Key: any minimal superkey that could serve as a primary key; Primary Key: the chosen candidate key; Super Key: any set of attributes that uniquely identifies a tuple (not necessarily minimal).

What distinguishes a Foreign Key from other keys?

A Foreign Key refers to a primary key in another relation; it enforces referential integrity but may be NULL and is not necessarily unique.