CSC 675 Review 2 - Entity Relationship Data Model, Relational DDL

Question 1

Explain how data models are used in the database design process.

Answer 1

Data models are used in the schema design.

Question 2

entity

Answer 2

correspond to “things” in the real world, each of which can be uniquely identified.

Question 3

attribute

Answer 3

Properties associated with entities or relationships

Question 4

attribute value

Answer 4

entity instances

Question 5

relationship instance

Answer 5

associate two or more entity instances together.

Question 6

composite attribute

Answer 6

one name, multiple named component attributes

Question 7

multivalued attribute

Answer 7

attribute can have multiple values

Question 8

derived attribute

Answer 8

Attribute which is related to another attribute value or entity, for example age/ birthdate, number of students/students.

Question 9

key attribute

Answer 9

Subset of attributes which uniquely determine an entity instance

Question 10

value set

Answer 10

(domain) - set of atomic values and type

Question 11

What is an entity type? *

Answer 11

(entities): groups of entities with the same attributes.

Question 12

What is an entity instance? *

Answer 12

Correspond to “things” in the real world, each of which can be uniquely identified.

Question 13

Explain the difference between an entity instance and an entity type. *

Answer 13

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 ).

Question 14

Explain the difference between an attribute and a value set (domain).

Answer 14

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

Question 15

What is a relationship type?

Answer 15

sets of relationship instances between entity instances of the same types. !

Question 16

Explain the difference between a relationship and a relationship type.

Answer 16

relationship is actual. type is abstract

Question 17

What is a participation role?

Answer 17

Signifies role that a participating entity from the entity type plays in each relationship means.

Question 18

When is it necessary to use role names in the description of relationship types?

Answer 18

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

Question 19

Describe the two alternatives for specifying structural constraints on relationship types.

Answer 19

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

Question 20

What are the advantages and disadvantages of specifying structural constraints on relationship types?

Answer 20

?

Question 21

What is meant by a recursive relationship type?

Answer 21

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).

Question 22

Give some examples of recursive relationship types.

Answer 22

A tutor is a student and teaches a student. manager is an employee and manages an employee.

Question 23

Owner entity key

Answer 23

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.

Question 24

Weak entity type

Answer 24

some entities do not have unique keys, although they do participate in a total relationship with another entity which does have a key.

Question 25

identifying relationship type

Answer 25

The relationship type that relates a weak entity type to its owner.

Question 26

partial key

Answer 26

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.

Question 27

Can an identifying relationship of a weak entity type be of degree greater than two? Give examples.

Answer 27

?

Question 28

When are weak entities used in data modeling?

Answer 28

do not have unique keys participate in a total relationship with another entity which does have a key.

Question 29

Why is it necessary for all entities in a schema to have key attributes?

Answer 29

So that they can be uniquely identified.

Question 30

Under what circumstances is it optional to specify structural constrains on a relationship? *

Answer 30

When you have a weak entity, participation is always total, there is no option

Question 31

Domain

Answer 31

The range of values of an attribute.

Question 32

Attribute

Answer 32

properties associated with entities or relationships

Question 33

Tuple *

Answer 33

Data set with specific instances in the range of each member. A row in a table.

Question 34

Relation Schema

Answer 34

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.

Question 35

Relationships ( Relation Instance )

Answer 35

associate two or more entity instances together

Question 36

Degree of a Relation

Answer 36

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.

Question 37

Relational database schema *

Answer 37

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.

Question 38

relational database instance

Answer 38

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.

Question 39

What are tuples in a relation not ordered? *

Answer 39

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.

Question 40

Why are duplicate tuples not allowed in a relation? *

Answer 40

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.

Question 41

What is the difference between a key and a super key?

Answer 41

Superkey is any subset of attributes that uniquely identifies the tuples of a relation. A key (better: primary key) is the minimal subset

Question 42

Why do we designate one of the candidate keys of a relation to be primary key?

Answer 42

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.

Question 43

Discuss the characteristics of relations that make them different from ordinary tables and files.

Answer 43

- The tuples are not considered to be ordered - the attributes in a relation are ordered. - All values are considered to be atomic or indivisible

Question 44

Define entity integrity

Answer 44

The entity integrity constraint states that no primary key value can be NULL and is considered to be very important.

Question 45

Why is entity integrity considered important?

Answer 45

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.

Question 46

Define referential integrity constrains

Answer 46

constraint that specifies that a record in one file must be related to records in other files.

Question 47

Why are referential integrity constrains considered important?

Answer 47

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.

Question 48

Foreign Key *

Answer 48

attribute that references another relation key 1. The attributes in the foreign key have the same domain as the primary key 2. A value of the foreign key occurred as the primary key of another or is null.

Question 49

What are foreign keys used for? *

Answer 49

to identify the different relationships between relations in a Relational Database Schema. (To link one table to another)

Question 50

List the various update operations on relations and the types of integrity constraints that must be checked for each update operation.

Answer 50

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