What is a database?
An organized collection of related data.
What software defines, creates, stores, retrieves, updates, and controls access to a database?
A Database Management System (DBMS).
What does a DBMS do to reduce data problems?
It reduces unnecessary data redundancy and inconsistency.
How does a DBMS protect data access?
It provides security and authorization.
How do DBMSs differ from file systems in managing data?
DBMSs provide integrated data management with controlled redundancy, authorization, data independence, and transaction, concurrency, and recovery facilities.
What does data abstraction do?
It hides unnecessary storage details and presents data at different levels.
What schemas make up the three-schema architecture?
External schemas, one conceptual schema, and an internal schema.
How does the relational data model represent data?
As tables with rows and columns.
What is a business rule?
A statement defining or restricting how an organization operates.
What is a transaction?
A logical unit of work containing one or more database operations.
What does the database design process collect before creating an ER design?
Requirements and business rules.
What is an entity?
A distinguishable real-world object.
What does an attribute describe?
An entity.
What does a relationship represent in an ER model?
An association between entities.
What does a 1:1 relationship allow each entity to relate to on the other side?
At most one entity.
What does an M:N relationship allow entities on both sides to do?
Relate to many entities on the other side.
What does total participation require of every entity in an entity set?
It must participate in the relationship.
What is the primary key of ENROLLMENT(StudentID, CourseID, EnrollDate)?
(StudentID, CourseID).
How is STUDENT ENROLLS_IN COURSE represented in the example?
As M:N relationship table ENROLLMENT(StudentID, CourseID, EnrollDate).
What is a weak entity?
An entity that cannot be uniquely identified by its own attributes and depends on an owner strong entity.
In a relation, what does each row represent?
A tuple.
What makes a candidate key different from a super key?
A candidate key is a minimal super key.
What does entity integrity prohibit for primary keys?
NULL values.
What does an insertion anomaly prevent?
Adding one fact without another unrelated fact.
What does a functional dependency mean?
Each value of determines exactly one value of .
When is a relation in 1NF?
When every cell contains one atomic value, with no repeating groups or multivalued fields.
What dependency must every non-prime attribute have on each candidate key for 2NF?
Full dependency on every candidate key.
What formal condition defines 3NF for every non-trivial ?
is a superkey or is a prime attribute.
Who proposed rules for relational DBMSs?
Codd.
What does Codd’s Rule 0 require a relational DBMS to do?
Manage the database entirely through its relational capabilities.
What does Codd’s dynamic online catalog rule require?
Relational metadata must be queryable.
What does Codd’s view-updatability rule require?
Theoretically updatable views must be updatable.
What operations must Codd’s set-level rule support?
Set-level insert, update, and delete.
What kind of query language is relational algebra?
A procedural query language specifying operations to obtain a required relation.
What does relational algebra’s selection operator choose?
Rows satisfying a condition.
What does relational algebra’s projection operator choose?
Columns.
What condition does a theta join use?
A general condition.
What rows does an inner join return?
Only matching rows.
What does division return from ?
The values associated with every required value in .
What kind of query language is relational calculus?
A non-procedural query language based on predicate logic.
What does the TRC expression represent?
Tuples for which predicate is true.
What do variables represent in Domain Relational Calculus?
Individual attribute values rather than whole tuples.
What does a database constraint do to stored data?
It restricts stored data to help maintain validity and consistency.
What must be true of a primary key under entity integrity?
It must never be NULL.
What must a foreign key reference under referential integrity?
An existing key value, or NULL if permitted.
What is a view in a database?
A named query that behaves like a virtual table.
What must happen to materialized view results when underlying data changes?
The results must be refreshed.
What components make up a database system?
The database, DBMS software, applications, users, and hardware.
Which software are examples of DBMSs?
MySQL, Oracle Database, PostgreSQL, Microsoft SQL Server, and SQLite.
What is one staffing limitation of a DBMS?
It requires trained staff.
What can happen when a DBMS fails?
The failure can affect many users.
What does the internal or physical level describe?
How data is stored, including files, indexes, and storage blocks.
What does the conceptual or logical level describe?
Stored data, its attributes, relationships, and constraints.
What does the external or view level present?
The database portion required by a particular user or application.
What does a query processor do with SQL?
It parses, validates, and optimizes SQL, then executes the query plan.
What metadata does a data dictionary or catalog store?
Metadata such as table definitions, columns, constraints, and users.
How does the hierarchical data model organize data?
As a tree of parent-child relationships.
What does the ER model represent for conceptual design?
Entities, attributes, and relationships.
What structures do semi-structured data models commonly use?
Flexible structures such as JSON or XML documents.
What are basic building blocks used by data models?
Entities or objects, attributes or properties, relationships, constraints, and operations.
Which database constraints can implement business rules?
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints.
What is an entity set?
A collection of similar entities.
What does a key attribute do in an ER model?
It uniquely identifies an entity.
What does a rectangle denote in common ER notation?
An entity.
What does an underlined attribute denote in common ER notation?
A key.
Where is InstructorID stored when each course has one instructor?
As a foreign key in COURSE.
Which attributes identify a DEPENDENT weak entity in the example?
EmployeeID and DependentName.
What does a relation’s degree measure?
Its number of attributes.
What does a relational database schema collect?
Relation definitions and their constraints.
In STUDENT(StudentID, Email, Name), what is Email if StudentID is primary?
An alternate key.
What does a CHECK constraint require?
A specified condition must be satisfied.
What does a DEFAULT constraint provide when no value is supplied?
A default value.
What does good relational design minimize?
Duplicated facts.
Why does a Student table storing PhoneNumbers as “111, 222” violate 1NF?
The PhoneNumbers field contains multiple values.
Which attributes have partial dependencies in the stated ENROLLMENT example?
StudentName depends on StudentID; CourseName depends on CourseID.
Which Codd rule represents all information as table values?
The Information rule.
Which Codd rule guarantees access to every atomic value by table, primary key, and column?
The Guaranteed access rule.
What does Codd’s NULL rule require for missing or inapplicable information?
Consistent handling.
What does relational algebra’s closure property guarantee about its operators?
They take relations as input and produce a relation as output.
What does relational algebra’s set difference return?
Rows in but not in .
What must be compatible for relational algebra’s union, intersection, and difference?
The relations must have the same number of attributes and compatible corresponding domains.
What does a join combine from two relations?
Related tuples.
Which rows does a right outer join retain?
All right rows and matching left rows.
What does a cross join pair with every left row?
Every right row.
Which students does return?
Students enrolled in every course listed in REQUIRED.
What does grouping do to tuples with identical grouping-attribute values?
It collects them into groups.
Which student tuples does return?
Student tuples with marks greater than 70.
What does the given DRC expression return for students scoring above 70?
The names of students scoring more than 70.
What does relational calculus use to express queries?
Predicates, variables, quantifiers, and logical conditions.
What does relational algebra make explicit that relational calculus does not?
The operational sequence of query operations.
What must a safe relational-calculus query produce?
A finite result based on values in the database.
What do domain constraints require of data values?
Values must have the correct type or range.
What range can a business-defined constraint require for Marks?
Marks can be required to be between 0 and 100.
What does the relational algebra operator ρ do?
It renames a relation or its attributes.
What tuples does relational algebra intersection R ∩ S return?
The tuples common to both relations.
Answer 37 questions on DBMS.
1. Which statements accurately distinguish a database from a DBMS?
2. A database architect is assigning responsibilities across the three-schema architecture. Which statements are correct?
Go over the full study guide for DBMS.
Read the study guide →Upload your class notes and Revizly writes your flashcards in about a minute.
Flashcard maker