CSC 675 Review 2 - Entity Relationship Data Model, Relational DDL Flashcards
Entity Relationship Data Model, Relational DDL
Explain how data models are used in the database design process.
Data models are used in the schema design.
entity
correspond to “things” in the real world, each of which can be uniquely identified.
attribute
Properties associated with entities or relationships
attribute value
entity instances
relationship instance
associate two or more entity instances together.
composite attribute
one name, multiple named component attributes
multivalued attribute
attribute can have multiple values
derived attribute
Attribute which is related to another attribute value or entity, for example age/ birthdate, number of students/students.
key attribute
Subset of attributes which uniquely determine an entity instance
value set
(domain) - set of atomic values and type
What is an entity type? *
(entities): groups of entities with the same attributes.
What is an entity instance? *
Correspond to “things” in the real world, each of which can be uniquely identified.
Explain the difference between an entity instance and an entity type. *
Student is an example of an entity type ( student_ID , Name, … ) Nicole is an example of an entity instance ( has all the attributes of a student and exists in the real world ).
Explain the difference between an attribute and a value set (domain).
Attributes are properties associated with an identity
domain - the collection of all possible values an attribute can have
Person has the attribute name, the domain is the set of all possible combinations of character strings that are a name
What is a relationship type?
sets of relationship instances between entity instances of the same types. !
Explain the difference between a relationship and a relationship type.
relationship is actual. type is abstract
What is a participation role?
Signifies role that a participating entity from the entity type plays in each relationship means.
When is it necessary to use role names in the description of relationship types?
Role names are not necessary when all participation entities are distinct the name of identity types generally specify the role played in each entity type
Describe the two alternatives for specifying structural constraints on relationship types.
We will refer to the cardinality ratio and participation constraints, taken together, as the structural constraints of a relationship type.
lower and upper bound on number of relationship instances in existence at all times (min = 0 -> partial, min > 0 -> total)
to specify minimum
and maximum numbers (min, max) on the participation of each entity type in a relationship type.
slides 51, 52, 53
What are the advantages and disadvantages of specifying structural constraints on relationship types?
?
What is meant by a recursive relationship type?
Each entity serves a particular role in the relationship. This allows the same entity to appear more than once in a relationship type (i.e. to participate in a recursive relationship).
Give some examples of recursive relationship types.
A tutor is a student and teaches a student. manager is an employee and manages an employee.
Owner entity key
Primary key of the strong entity in the week entity, foreign key
it must also have least one subset of attributes which distinguish multiple entities with the same owner key.
Weak entity type
some entities do not have unique keys, although they do participate in a total relationship with another entity which does have a key.
identifying relationship type
The relationship type that relates a weak entity type to its owner.
partial key
just part of a key - some proper subset of the key attributes
In weak entities it is the attribute that can uniquely identify weak entities that are related to the same owner entity.
Can an identifying relationship of a weak entity type be of degree greater than two? Give examples.
?
When are weak entities used in data modeling?
do not have unique keys
participate in a total relationship with another entity which does have a key.
Why is it necessary for all entities in a schema to have key attributes?
So that they can be uniquely identified.
Under what circumstances is it optional to specify structural constrains on a relationship? *
When you have a weak entity, participation is always total, there is no option
Domain
The range of values of an attribute.
Attribute
properties associated with entities or relationships
Tuple *
Data set with specific instances in the range of each member. A row in a table.
Relation Schema
A relation schema is represented by R(A1, A2, …) where R is called the name of the relation and A1, A2, … are the attributes of the schema.
It is representation of database highlighting relationships that we have created.
Relationships ( Relation Instance )
associate two or more entity instances together
Degree of a Relation
The degree (or arity) of a relation is the number of attributes n of its relation schema. For example, STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa) has degree seven.
Relational database schema *
the schema defines the tables, columns and relationships that make up a relational database.
a description of the database
Is represented as S = {R1, R2, …} where R1, R2, … are relation schemas. It also consists of a set of Integrity constraints.
relational database instance
An instance of relational database schema S ={R1, R2, …, Rm} is a set of relation instances {r1, r2, …, rm} such that each relation instance ri is a state of corresponding relation schema Ri.
In addition, each relation instance must satisfy the integrity constraints specified in the relational database schema.
What are tuples in a relation not ordered? *
A relation is defined as a set of tuples. Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order. In other words, a relation is not sensitive to the ordering of tuples.
Why are duplicate tuples not allowed in a relation? *
Duplicate tuples are not allowed in a relation because it violates the specifications of the relational integrity constraints, particularly the key constraint which states that no two tuples can have the same values for their attributes at any relation state of a database.
What is the difference between a key and a super key?
Superkey is any subset of attributes that uniquely identifies the tuples of a relation.
A key (better: primary key) is the minimal subset
Why do we designate one of the candidate keys of a relation to be primary key?
When there is more than one key in a relation schema of a database, all these keys are referred to as candidate key. We designate a primary key because it is easier to deal with a database that has a single distinct key for a relation.
Discuss the characteristics of relations that make them different from ordinary tables and files.
- The tuples are not considered to be ordered
- the attributes in a relation are ordered.
- All values are considered to be atomic or indivisible
Define entity integrity
The entity integrity constraint states that no primary key value can be NULL and is considered to be very important.
Why is entity integrity considered important?
because the primary key is used to identify individual tuples
having a null values for the primary key implies that we cannot identify some tuples.
Define referential integrity constrains
constraint that specifies that a record in one file must be related to records in other files.
Why are referential integrity constrains considered important?
The referential integrity constrain is specified between two relations and is used to maintain the consistency among the tuples in the two relations. Without the referential integrity constrain the database could be left in an invalid state.
Foreign Key *
attribute that references another relation key
- The attributes in the foreign key have the same domain as the primary key
- A value of the foreign key occurred as the primary key of another or is null.
What are foreign keys used for? *
to identify the different relationships between relations in a Relational Database Schema.
(To link one table to another)
List the various update operations on relations and the types of integrity constraints that must be checked for each update operation.
insert: may violate key constraint, entity integrity constraint or referential integrity.
delete: can only violate referential integrity.
modify: only a problem when PK or FK is modified
