CS 350 Quiz 4
Two methods to express schema
1. Short text statements, in which each relation is named and the names of its attributes follow in parentheses 2. A graphical representation, in which each relation is represented by a rectangle containing the attributes for the relation
Characterize six important properties of relations
1. each relation in a database has a unique name 2. an entry at the intersection of each row and column is single valued. There can only be one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation 3. each row is uniquely identified 4. each attribute within a table has a unique name 5. the sequence of columns is insignificant 6. the sequence of rows is insignificant
Properties of relations
1. each relation in a database has a unique name 2. an entry at the intersection of each row and column is single valued. There can only be one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation 3. each row is uniquely identified 4. each attribute within a table has a unique name 5. the sequence of columns is insignificant 6. the sequence of rows is insignificant
Relation
A named, two dimensional table of data
Surrogate primary key
A new attribute for a weak entity
Well-structured relation
A relation that contains minimal redundancy and allows users to insert, modify, and delete rows in a table without errors or inconsistencies
What is a well-structured relation? Why are they important?
A relation that contains minimal redundancy and allows users to insert, modify, and delete rows in a table without errors or inconsistencies. They are important because they prevent redundancy and inconsistency.
Referential integrity constraint
A rule that maintains consistency among the rows of two relations. The rule states that if there is a foreign key in one relation, either each foreign key value must match a primary key value in another relation or the foreign key value must be null
Domain constraints
All of the values that appear in a column of a relation must be from the same domain
Transform multivalued attribute
Create a separate relation for multivalued attribute with composite primary key, including the primary key of the entity
Major types of integrity constraints
Domain constraints, entity integrity, and referential integrity
Weak entities
Entities that cannot exist except with an identifying relationship with an owner entity type
Anomalies
Errors or inconsistencies caused by redundancies
Describe the differences between the conceptual and logical models
The conceptual model is created to gain an understanding of the business and its rules. A logical model is created to translate the conceptual design into a logical database design that can be implemented in a chosen database system
Insertion anomaly
When the primary key is a combination of two different relation attributes, so in order to add a new row, information about both attributes is necessary
Modification anomaly
When there is repetition in rows, data for that row must be updated or modified twice
Composite key
a primary key that consists of more than on attribute
Data structure
data are organized in the form of tables, with rows and columns
Data manipulation
powerful operations (SQL) are used to manipulate data stored in the relations
Schema
the way the structure of the database is described
Describe three types of anomalies that can arise in a table and the negative consequences of each
An insertion anomaly occurs when the primary key is a combination of two different relation attributes, so in order to add a new row, information about both relations is necessary. A negative to this is that information about both relations is not always known. A deletion anomaly occurs when a row contains information related to another relation, so that when a row is deleted, information on two relations is lost. A negative to this is losing data unnecessarily. A modification anomaly occurs when there is repetition in rows, so data for that row must be updated or modified twice. A negative is the risk of inconsistent data.
Transform supertype/subtype
Create a relation for the superclass, which contains the primary and all nonkey attributes in common with all subclasses, plus create a separate relation for each subclass with the same primary key (with the same local name) but with only the nonkey attributes related to that subclass
Transform weak entity
Create a relation with a composite primary key (which includes the primary key of the entity on which this entity depends) and nonkey attributes
Transform binary or unary M:N relationship or associative entity without its own key
Create a relation with a composite primary key using the primary keys of the related entities plus any nonkey attributes of the relationship or associative entity
Transform regular entity
Create a relation with primary key and nonkey attributes
Transform binary or unary M:N relationship or associative entity with its own key
Create a relation with the primary key associated with the associative entity plus an nonkey attributes of the associative entity and the primary keys of the related entities as foreign keys
Regular entities
Entities that have an independent existence and generally represent real world objects
Associative entities
Formed from many to many relationships between other entity types
How do you represent a 1:M unary relationship in a relational data model?
Place the primary key of the entity on the one side of the relationship as a foreign key in the relation for the entity on the many side
Transform binary or unary 1:M relationship
Place the primary key of the entity on the one side of the relationship as a foreign key in the relation for the entity on the many side
Entity integrity
States that no primary key attribute may be null. Designed to ensure that every relation has a primary key and that the data values for that primary key are all valid; it guarantees that every primary key is non-null
Transform ternary and n-ary relationships
Without its own key, include as part of primary key of relation for the relationship or associative entity the primary keys from all related entities; with its own surrogate key, the primary keys of the associative entities are included as foreign keys in the relation for the relationship or associative entity
Foreign key
an attribute in a relation that serves as the primary key of another relation
Primary key
an attribute or a combination of attributes that uniquely identifies each row in a relation
Four reasons to create instance with sample data
1. It allows you to test your assumptions regarding the design 2. It provides a convenient way to check the accuracy of your design 3. It helps improve communication with users in discussing your design 4. It can be used to develop prototype applications and to test queries
Null
A value that may be assigned to an attribute when no other value applies or when the applicable value is unknown
Three components of relational data models
Data structure, data manipulation, data integrity
Transform composite attribute
Each component of a composite attribute becomes a separate attribute in the target relation
Three major types of anomalies
Insertion anomaly, deletion anomaly, and modification anomaly
Transform binary or unary 1:1 relationship
Place the primary key of either entity in the relation for the other entity; if one side of the relationship is optional, place the foreign key of the entity on the mandatory side in the relation for the entity on the optional side
Relational data model
Represents data in the form of tables
Deletion anomaly
When a row contains information related to another relation, so that when a row is deleted, information on two relations is lost
Data integrity
the model includes mechanisms to specify business rules that maintain the integrity of data when they are manipulated
When to create a surrogate primary key?
- there is a composite primary key - the natural primary key is inefficient - the natural primary key is recycled and can't be guaranteed to be unique overtime
