Quiz 1 Flashcards
attribute
An entity is described (in DB) using a set of attributes.
domain
For each attribute associated with an entity set, we must identify a domain of possible values
entity
Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes.
relationship
A collection of similar entities. E.g., all employees.
All entities in an entity set have the same set of attributes. (Until we consider ISA hierarchies, anyway!)
Each entity set has a key.
Each attribute has a domain.
relationship set
Collection of similar relationships.
An n-ary relationship set R relates n entity sets E1 … En; each relationship in R involves entities e1 E1, …, en En
Same entity set could participate in different relationship sets, or in different “roles” in same set.
key constraint
A restriction on the relationships an entity can have. Can include 1-to-1, 1-to-Many, Many-to-1, or Many-to-Many
participation constraint
Does every department have a manager?
If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial).
Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value!)
overlap constraint
Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed)
covering constraint
Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)
weak entity set
A weak entity can be identified uniquely only by considering the primary key of another (owner) entity.
Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities).
Weak entity set must have total participation in this identifying relationship set.
ISA hierarchy
If we declare A ISA B, every A entity is also considered to be a B entity.
Reasons for using ISA:
To add descriptive attributes specific to a subclass.
To identify entitities that participate in a relationship
aggregation
Used when we have to model a relationship involving (entity sets and) a relationship set.
Allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships.
role indicator
If an entity set plays more than one role, the role indicator concatenated with an attribute name from the entity set gives us a unique name for each attribute in the relationship set. For example, the Reports_To relationship
set has attributes corresponding to the ssn of the supervisor and the ssn of the subordinate, and the names of these attributes are supervisor_ssn and
subordinate_ssn.
Name the main steps in database design. What is the goal of each step?
In which step is the ER model mainly used? (Section 2.1)
- Requirements Analysis: The very first step in designing a database application is to understand what data is to be stored in the database, what applications must be built on top of it, and what operations are most frequent and subject to performance requirements.
- Conceptual Database Design: The information gathered in the requirements analysis step is used to develop a high-level description of the data to be stored in the database, along with the constraints known to hold over
this data. - Logical Database Design: We must choose a DBMS to implement our database design, and convert the conceptual database design into a database schema in the data model of the chosen DBMS.
- Schema Refinement: The fourth step of database design is to analyze the collection of relations in our relational database schema to identify potential problems, and to refine it. In contrast to the requirements analysis and conceptual design steps, which are essentially subjective, schema refinement can be guided by some elegant and powerful theory.
- Physical Database Design: In this step, we consider typical expected workloads that our database must support and further refine the database design to ensure that it meets desired performance criteria. This step may
simply involve building indexes on some tables and clustering some tables, or it may involve a substantial redesign of parts of the database schema obtained from the earlier design steps. - Application and Security Design: Any software project that involves a DBMS must consider aspects of the application that go beyond the database itself. Design methodologies like UML (Section 2.7) try to address
the complete software design and development cycle. Briefly, we must identify the entities (e.g., users, user groups, departments) and processes involved in the application. We must describe the role of each entity in every process that is reflected in some application task, as part of a complete workflow for that task. For each role, we must identify the parts of the database that must be accessible and the parts of the database that must
not be accessible, and we must take steps to ensure that these access rules are enforced.
The ER Model is used in the first three steps
Entity vs Attribute
Should address be an attribute of Employees or an entity (connected to Employees by a relationship)?
Depends upon the use we want to make of address information, and the semantics of the data:
If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued).
If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
