Relational Data Model

Question 1

What is a relational database?

Answer 1

A set of relations

Question 2

What are the two parts of a relation?

Answer 2

Instance (table) and Scheme (name, plus attributes)

Question 3

What is the cardinality of a relation?

Answer 3

Number of Rows

Question 4

What is the degree of a relation?

Answer 4

Number of columns

Question 5

What is the DDL used for?

Answer 5

Creation, Deletion, Updating, Truncate, Rename relation

Question 6

What is the simple form of creating a relation?

Answer 6

CREATE TABLE ( ssn INTEGER );

Question 7

What are the 4 most common data types?

Answer 7

INT, REAL, CHAR(n), VARCHAR(n)

Question 8

What is the form of the data value?

Answer 8

yyyy-mm-dd

Question 9

What is the form of the time value?

Answer 9

hh:mm:ss.ss

Question 10

What is the syntax to delete a relation?

Answer 10

DROP TABLE ;

Question 11

How do you add any existing attribute to a relation scheme

Answer 11

ALTER TABLE ADD

Question 12

How do you remove an attribute from a relation?

Answer 12

ALTER TABLE DROP

Question 13

How do you insert a single tuple?

Answer 13

INSERT INTO Students(sid, name) VALUES (53, ‘Smith’)

Question 14

Do you delete all tuples satisfying some condition?

Answer 14

DELETE
FROM Students S
WHERE S.name = ‘Smith’

Question 15

What are integrity constraints?

Answer 15

Conditions that must be true for any instance of the database

Question 16

When are ICs specified?

Answer 16

When schemes are defined

Question 17

When are ICS checked?

Answer 17

When relations are modified?

Question 18

How are keys declared?

Answer 18

Using the PRIMARY KEY or UNIQUE keywords after a single attribute or after all attributes using PRIMARY KEY(attr1, attr2)

Question 19

What’s the difference between PRIMARY KEY and UNIQUE?

Answer 19

Exactly 1 PK, but 0+ UNIQUE

NULLS not allowed in PK, but UNIQUE can have NULLs

Question 20

How do you force a value to exist and enforce total participation?

Answer 20

NOT NULL

Question 21

What is a foreign key?

Answer 21

A set of fields in one relation that is used to refer to a tuple in another relation

Question 22

How do you specify FOREIGN KEY?

Answer 22

FOREIGN KEY (sid) REFERENCES STudents

Question 23

What are the options for foreign key violations?

Answer 23

NO ACTION, CASCADE, SET NULL, SET DEFAULT
Ex ON DELETE CASCADE
ON UPDATE SET DEFAULT

Question 24

How to convert an entity to a relation?

Answer 24

CREATE TABLE ( PRIMARY KEY );

Question 25

How to convert a relationship to a relation?

Answer 25

Attributes must include the keys of each entity and any additional descriptive attributes. Add foreign key for the keys of the entities

Question 26

How to convert Many to One relationships to relations?

Answer 26

Combine both entities into the “many”;
Ex: Given many drinkers and 1 favorite beer:
Drinkers(name, addr, favBeer)

Question 27

Can we combine many-many relationships?

Answer 27

No, creates much redundancy

Question 28

How to convert a weak entity set to relations?

Answer 28

Combine the relationship and weak entity into one relation, adding the foreign key of the supporting entity as a relationship

Question 29

How do enforce total participation?

Answer 29

use NOT NULL (especially on foreign keys)

Question 30

What are the 3 approaches to subclasses?

Answer 30

1. Object Oriented: one relation per subset of subclasses with all relevant attributes
2. Use nulls: one relation, use NULLs in attributes that don’t belong
3. E/R: one relation for each subclass (key attributes, attributes of that subclass)

Question 31

What is a view?

Answer 31

A relation that stores a definition, rather than a set of tuples. Used to present necessary information while hiding details of the underlying relations (security)