Cmpt 354 - The Relational Model

Front 1

Do database tables have unique names?

Back 1

Yes.

Front 2

What does a table in a relational database represent? What do its rows represent?

Back 2

1) Tables correspond to relations.

2) Each row represents a single entity or relationship (tuples).

Front 3

Relations consist of ________ and ________.

Back 3

1) A relation instance.

2) A relation schema.

Front 4

What is a relation instance?

Back 4

An actual with table with a particular set of rows.

Front 5

What is a relation schema?

Back 5

The column headings of the table.

Front 6

What does a schema consist of?

Back 6

1) The relation's name.

2) The name of each column in the table.

3) The domain of each field/attribute (column).

eg. Customer = {sin, firstName, lastName, age, income}

Front 7

Draw the relation instance for:

Customer = {sin, firstName, lastName, age, income}

Back 7

Front 8

What is the domain of a relation instance?

Back 8

A subset of the Cartestian product of the list of domains.

In practice a relation instance is usually some small subset of the Cartesian product.

Front 9

What is a relationship instance?

Back 9

A set of records (tuples) that has the same number of fields as the schema.

Front 10

What is the degree of a relation?

Back 10

The number of fields.

Front 11

What is the cardinality of a relation instance?

Back 11

The number of records it contains.

Front 12

What does SQL stand for?

Back 12

Structured Query Language.

Front 13

What is the DML and what does it do?

Back 13

Data manipulation language which allows users to create, modify and query data.

Front 14

What is the DDL and what does it do?

Back 14

Data definition language is used to define external and conceptual schemas.

The DDL supports the creation, deletion and modification of tables including the specification of domain constraints and other constraints.

Front 15

What statement is used to create a table in SQL?

Back 15

Front 16

Is SQL case sensitive?

Back 16

SQL keywords are not. Name objects may be.

Front 17

What statement is used to insert a record into an existing table?

Back 17

Front 18

In regards to insertion of a record into a table, what can be said about the list of column names? Data for some columns?

Back 18

The list of column names is optional but the values must be in the same order as the columns.

Data for some columns may be omitted in which case the column names must be specified.

Front 19

What statement is used to delete a record?

Back 19

The WHERE clause specifies the record(s) to be deleted.

Omitting this deleted all the records in a table.

Front 20

What statement is used to modify a record(s) in a table?

Back 20

Front 21

What statement is used to delete a table? What is important about this?

Back 21

DROP TABLE

This also deletes the table schema.

Front 22

What statement is used to add or remove columns?

Back 22

ALTER TABLE

ADD height INTEGER

ALTER TABLE

DROP height

Front 23

What is an integrity constraint? When should they be specified?

Back 23

Restrict the data that can be stored in a DB to prevent invalid data being added to the DB.

When the DB schema is defined.

Front 24

What are four types of constraints?

Back 24

1) Domain constraint.

2) Key constraint.

3) Foreign key constraint.

4) General constraint.

Front 25

What is a key constraint?

Back 25

States that a minimal subset of the fields in a relation is unique.

Front 26

What statements are used to specify key constraints?

Back 26

UNIQUE - identifies candidate keys.

PRIMARY KEY - identifies the primary key.

Front 27

What are candidate keys useful for?

Back 27

Preventing duplicate data from being entered into the DB.

Front 28

Create a Patient table with sin as primary key, msp as candidate key, firstName, lastName, age.

Back 28

Front 29

What does a foreign key constraint do?

Back 29

References the primary key of another relation.

The value of the foreign key attribute(s) must match a primary key in the referenced table.

Front 30

Specify this.

Back 30

CREATE TABLE Account (

accountNumber INTEGER,

type CHAR(5),

balance REAL,

customerSIN CHAR(11),

PRIMARY KEY(accountNumber),

CONSTRAINT fk_customer FOREIGN KEY

(customerSIN) REFERENCES Customer)

Front 31

What are two types of general constraints?

Back 31

1) Table constraints associated with a single table.

2) Assertions which may involve several tables and are checked when any of these tables are modified.

Front 32

What are two ways a primary key constraint can be violated?

Back 32

1) A record can be changed or inserted so that it duplicates the primary key of another record in the table.

2) A record can be changed or inserted so that (one of) the primary key attributes is null.

Both cases lead to a rejected transaction.

Front 33

A deletion or update transaction in the referenced table may violate a foreign key. Name four responses possible in SQL.

Back 33

1) NO ACTION - reject transaction (default).

2) CASCADE - delete or update referencing record.

3) SET NULL - set the referencing record's foreign key attribute(s) to null (only on deletion).

4) SET DEFAULT - set the referencing record's foreign key attribute(s) to a default value (only on deletion). The default value must be specified in the foreign key.

Front 34

A table that represents an entity set should have which four characteristics?

Back 34

1) One column for each attribute.

2) The domain of each attribute should be known and specified in the table.

3) The primary key should be specified in the table.

4) Other constraints that have been identified outside the ER model should also be created where possible.

Front 35

Create the table in SQL.

Back 35

Front 36

What are the two attributes of a relationship set?

Back 36

1) The primary keys of the participating entity sets.

2) Any descriptive attributes of the relationship set.

Front 37

What do the mapping cardinalities of the entities involved in a relationship determine?

Back 37

1) The primary key of the relationship.

2) Whether or not the relationship needs to be represented as a separate table.

Front 38

How should foreign keys be created?

Back 38

Created for the attributes derived from the participating entity sets in a relationship set.

Front 39

A relationship set with no cardinality constraints requires what?

Back 39

A separate table to represent a relationship with no cardinality constraints (ie. many-to-many).

Front 40

What is the primary key of a relationship set with no cardinality constraints?

Back 40

A compound key made up of the primary keys of the participating entity sets.

Front 41

Create the table.

Back 41

Front 42

Create the table.

Back 42

Front 43

For a relationship set with cardinality constraints...

1) If the relationship is one-to-one, what can be said about its primary key?

2) If the relationship is many-to-one, what can be said about its primary key?

Back 43

1) The primary key can be either of the primary keys, but only one.

2) The primary key is from the entity set that can only appear once.

Front 44

1) If the relationship set has no cardinality constraints, the primary key is considered ______?

2) If the relationship set has at least one cardinality constraint ______?

Back 44

1) A compound key.

2) The primary key is taken from the entity that can be involved in at most one relationship.

Front 45

1) When does a binary relationship not require a table?

2) How do we represent this in a table?

Back 45

1) At least one of its entities has a key constraint.

2) Add the relationship set's attributes to the table for the entity set that provided the primary key. The attributes from the other entity sets involved in the relationship are specified as foreign keys.

Front 46

Example of many-to-one.

Back 46

1) Since an employee can only work in one branch there is still only one row for each employee in the Employee table.

2) No table is created for the works_in relationship.

3) Attributes folded into Employee table from works_in and Branch primary key.

Front 47

Where possible, participation constraints should be included in a table specification.

When should you declare attributes NOT NULL?

What are other ways to model PCs?

Back 47

This approach only works when a relationship is not represented in a separate table.

Some participation constraints must be modeled using assertions or triggers.

Front 48

Study the examples in the slides.

Back 48

OK.

Front 49

What are three characteristics of weak entity sets?

Back 49

1) Total participation in the identifying relationship.

2) A cardinality constraint with the identifying relationship.

3) A partial key.

Front 50

A weak entity set should ___________ and __________?

Back 50

1) Include a foreign key (to its owner entity set), the attributes of which are part of the WES' primary key.

2) Specify the foreign key as ON DELETE CASCADE.

Front 51

Back 51

Weak entity set example.

Front 52

What are two basic approaches to translating class hierarchies to tables?

Back 52

1) Create separate tables for the superclass and for each subclass. Superclass only contain its own attributes. Subclass contain primary key attributes of superclass and its own attributes. Cascade deletion of superclass records to subclasses.

2) Create tables for the subclass only which contain their attributes and all of the attributes of the superclass. The primary key is the primary key of the superclass. This assumes that there are no entities in the superclass that are not entities in a subclass.

Front 53

How is an aggregate entity represented in a table?

Back 53

A table defining the relationship set in the aggregation.

Front 54

The relationship set between the aggregate entity and the other entity has the following attributes _________?

Back 54

1) The primary key of the participating entity set.

2) The primary key of the relationship set that defines the aggregate entity.

3) Its own descriptive attributes (if any).

Front 55

In what case is no table required for an aggregate entity?

Back 55

1) If there is total participation between the aggregate entity and its relationship AND

2) If the aggregate entity does not have any descriptive attributes.

--> Insert the attributes of the aggregate entity into the table representing the relationship with that entity.

Front 56

What are views useful for?

Back 56

1) Convenience.

2) Security.

3) Independence.

Front 57

Consider a DB containing these two tables:

Branch =(branchName, managerSIN, budget) Employee = (sin, name, salary, birthDate, branchName) , where branchName in Employee is a foreign key

A view is needed to access the employee's SIN,name and manager’s SIN

Back 57

Front 58

Generally, how are updates to views allowed?

Back 58

Derived from a single table.

Front 59

What is a superkey?

Back 59

Two distinct tuples in a relation instance, cannot have identical values in all the fields of a superkey (or if they do they must have all of the exact same values).

Front 60

slide 68 (functional dependencies)

Back 60

slide 68 (functional dependencies)