CHAP 3
Why should one not skip ER modeling to directly create a relational schema or in other words, why is ER modeling a good practice before relational schema is created or before a database is implemented? p. 86
-ER modeling is more suited for visualization of the requirements -Certain concepts can be visualized graphically only in ER diagrams -Every attribute is mentioned only once in the ER diagram -An ER model is a better communication and documentation device
List the six conditions that must hold for a table to be a relation. p. 57-58
1. Each column must have a name (within one table, each column name must be unique) 2 .Within one table, each row must be unique 3 .Within each row, each value in each column must be single valued (multiple values of the content represented by the column are not allowed in any rows of the table) 4. All values in each column must be from the same (predefined) domain 5. Order of columns is irrelevant 6. Order of rows is irrelevant
How is 1:1 relationship mapped? p. 68-69 **
1:1 relationships are mapped in the same way as 1:M relationships One of the resulting relations will have a foreign key pointing to the primary key of another resulting relation One of the mapped relations is chosen to have a foreign key referring to the primary key of the other mapped relation In cases when there is no particular advantage in choosing which resulting relation will include a foreign key, the choice can be arbitrary In other cases one choice can be more efficient than the other for example between a mandatory and optional choosing the mandatory foreign key is recommended beau case it will result in fewer nulls.
What is a relational database? What is other name for a 'relation' ? What is a tuple? p. 57
A relational database is a collection of related relations within which each relation has a unique name. A relation is sometimes referred to as a relational table or sometimes just a table. A tuple is a row in a relation it is also called a record. Note: Every relation is a table but not every table is a relation.
How do you map an entity with a multi-valued attribute? p. 72-73 **
An entity containing the multivalued attribute is mapped without the multi-valued attribute The multi-valued attribute is mapped as a separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself Both of these columns form a composite primary key for the separate relation
How do you map associative entities? p. 83 **
Associative entities are mapped into relational database constructs in the identical way as M:N relationships. Essentially a new relation is created for the associative entity. With foreign keys pointing to the corresponding primary keys. These foreign keys make up the composite primary key.
How do you map multiple relationship between the same entities? p. 76 **
Essentially each relationship is mapped. Ask ARPAN about this?
What are implicit constraints? Provide two examples
Implicit Constraints are the implicit relational database model rules that a relational database must satisfy in order to be valid -Each relation in a relational schema must have a different name -Each relation must satisfy the following conditions: -Each column must have a different name -Each row must be unique -In each row, each value in each column must be single valued -Domain constraint - all values in each column must be from the same predefined domain -The order of columns is irrelevant -The order of rows is irrelevant -Primary key constraint - each relation must have a primary key, which is a column (or a set of columns) whose value is unique for each row -Entity integrity constraint -Referential integrity constraint
What is an entity integrity constraint? p. 62
In a relational table, no primary key column can have null (empty) values The entity integrity constraint is a rule stating that no primary key column can be optional Every RBMS enforces this rule Note:This also applies to composite primary keys
How do you map M:N unary relationship p. 75 **
In addition to the relation representing the entity involved in a unary M:N relationship, another relation is created to represent the M:N relationship itself This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship Each of the foreign keys is used as a part of the composite primary key of the new relation
How is M:N relationship mapped? p. 66-68 **
In addition to the two relations representing the two entities involved in the M:N relationship, another relation is created to represent the M:N relationship itself This new relation has two foreign keys, corresponding to the primary keys of the two relations representing the two entities involved in the M:N relationship The two foreign keys form the composite primary key of the new relation WHAT IF ON PASGE 67 WE HADE A MANDOTRY OPTIONAL
What is a referential integrity constraint?
In each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null (empty). A rule that defines values that are valid for use in foreign keys
What is a foreign key? p. 63
Is a column in a relation that refers to a primary key column in another (referred) relation A mechanism that is used to depict relationships in the relational database model For every occurrence of a foreign key, the relational schema contains a line pointing from the foreign key to the corresponding primary key
What is a relational schema? p. 57
It is a visual depiction of the relational database model. Note. The relational database model is a logical database model that represents a database as a collection of related tables
Define a primary key. Provide an example. p. 59
It is column (or a set of columns) in a relation whose value is unique for each row Each relation must have a primary key The name of the primary key column is underlined in order to distinguish it from the other columns in the relation For example in the relation CUSTOMER the CustomerID is the primary key
What are some of the contemporary commercial relational DBMS packages? p. 57
Oracle, MySQL,Microsoft SQL Server, PostgreSQL, Teradata, IBM DB2, and Microsoft Access.
How do you map ternary relationships? p. 84 **
Ternary relationships are used as many-to-many-to-many relationships A new relation is created with foreign keys from the participating entities forming a composite primary key of the new relation
How do you map 1:1 unary relationship? p. 76 **
The relation mapped from an entity involved in a 1:1 unary relationship contains a foreign key that corresponds to its own primary key. 1 to one foreign key will be unique
How do you map 1:M unary relationship? p. 74-75 **
The relation mapped from an entity involved in a 1:M unary relationship contains a foreign key that corresponds to its own primary key.
How is 1:M relationship mapped? How is mandatory and optional participation handled? p. 64-65 **
The relation mapped from the entity on the M side of the 1:M relationship has a foreign key that corresponds to the primary key of the relation mapped from the 1 side of the 1:M relationship. ASK ARPAN HOW TO ANSWER THIS
What are user defined constraints? Provide two examples
They are the Database constraints that are added by the database designer For example having an optional attribute or having a mandatory foreign key Business rules are User defined constraints that specify restrictions on databases that are not a part of the standard notation for creating ER diagrams For example if you have the attribute salary then the salary being between 50k and 200k is a business rule.
How do you map a weak entity? p. 77 **
Weak entities are mapped in a same way as regular entities with one addition: The resulting relation has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity If the weak entity has two owner entities then both the foreign keys and the partial identifier are used to make the composite primary key. Unless it is a 1:1 identifying relationship between the weak entity and its owner entity then the foreign key just becomes the primary key.
When do you use a designer-created primary key? Provide an example. p. 84-85
You normally use it when you don't want to use another candidate key. Like when the primary key column, not called for by the original requirements, added to a table by the database designer Often used in conjunction with the autonumber data type option For e.g. in the relation patient we have the candidate key SSN but we don't want to use the SSN as a primary key so we create one.
What is a composite primary key. Provide an example. p. 61
is a primary key that is composed of multiple columns Column names of a composite primary key are underlined, because combined together they form the primary key For example in the relation CLASSROOM the columns Building and RoomNumber form the composite primary key called ClassroomID