Lecture 2

Question 1

Relational DBMS

Answer 1

Most DBMS are networked and multi-user, therefore:

• Concurrent writes to files
• Network interruptions
• Database downtime while users are connected
• Requesting changing data
• Managing computational resources
• Authentication and authorisation

Question 2

ACID

Answer 2

An important property of a multi-user DBMS.

• Atomic: Transactions are all or nothing.
• Consistent: Only valid data is saved
• Isolated: Simultaneous transactions are independently performed
• Durable: Transactions are never lost

Question 3

A transaction

Answer 3

A unit of work that is executed as a single entity.

Question 4

Relational approach to a database (Reminder)

Answer 4

Organises data into rows and columns, which collectively from a table. Data is structured across multiple tables, which can be joined with the use of primary and foreign keys.

Question 5

Entity

Answer 5

Something of interest to the use of the database. e.g Customer, Student, Geolocation

Question 6

Cell

Answer 6

An individual piece of data stored in a table.

Question 7

Field

Answer 7

A property of an entity. (column of a table)

Question 8

Row / Record

Answer 8

An instance of an entity, a collection of cells representing an individual entity.

Question 9

Result-set

Answer 9

A non-persistent table, the result of a query (What you’ll be working with in code)

Question 10

Primary Key

Answer 10

One or more columns that together can be used as a unique identifier for each row in a table.

Question 11

Foreign Key

Answer 11

One or more columns that together are used to identify a record in another table.

Question 12

Relation integrity constraints (3 constraints)

Answer 12

1. Domain constraints
2. Key constraints
3. Referential Integrity Constraints

Question 13

Domain constraints for Relational Models

Answer 13

Domain constraints can be violated if an attribute value is not appearing in the corresponding domain or it is not of the appropriate data type.

• Constraints on whether a cell can be NULL
• Constraints on the data type records

Question 14

Key constraints for Relational Models

Answer 14

Primary key attributes must be unique for different records in the relation.

Question 15

Primary Key

Answer 15

• Highly stable, unique identifier.
• Cannot have null attributes.

Altering a table:
ALTER TABLE us_states ADD PRIMARY KEY (state_code);

Creating a table:
CREATE TABLE us_states (
InvoiceNo int NOT NULL,
CustomerID int NOT NULL,
Amount int,
PRIMARY KEY (InvoiceNO)
);

Question 16

Candidate Key

Answer 16

• Unique identifier fields, are columns in the table where their values are unique and can be assigned as primary key.
• Can never be null or empty. And its value should be unique.
• Minimal super key.

Question 17

Super Key

Answer 17

Consists of candidate keys attributes + extra attributes

Question 18

Foreign Key

Answer 18

Foreign key constraints reference to keys in other tables, consternating the values of the table.

Denotes a strict relation between two entities.

ALTER TABLE Voters
ADD FOREIGN KEY (State_Voter_Registration)
REFERENCES US_States(State_code);

Question 19

Simple and Composite Keys

Answer 19

If a key, let’s say the PK has more than one field, it is a composite key.

If it only refers to one field, it is a simple key.

Question 20

Defining behaviour on FK’s:

Answer 20

We can define a behaviour when a certain field is deleted.

• Restrict DELETION
• UPDATE all linked records, CASCADE
• SET NULL value
• NO ACTION
• SET DEFAULT value

e.g:
REFERENCES teachers(ID) ON DELETE RESTRICT ON UPDATE CASCADE,

Question 20

Defining behaviour on FK’s:

Answer 20

We can define a behaviour when a certain field is deleted.

• Restrict DELETION
• UPDATE all linked records, CASCADE
• SET NULL value
• NO ACTION
• SET DEFAULT value

e.g:
REFERENCES teachers(ID) ON DELETE RESTRICT ON UPDATE CASCADE,

Question 21

Entity Relationship Model

Answer 21

ER is a way to organise the description of entities and the relationships between them.

• Entities, all with characteristic attributes.
• For each kind of entity there are instances that combine to an entity set.
• A key as a minimal set of attributes to identify and distinguish entity instances.
• Relationships between entities.

Question 22

Entity Relationship Model entities:

Answer 22

one-to-one: Each A may relate to one B
many-to-one: Several A may relate to one B
one-to-many: Each A may relate to several B
many-to-many: Any number of A can relate to any number of B.

Question 23

ER diagrams:

Answer 23

Entity - rectangle
Relationship - diamond
Attribute - circle
Key attribute - circle with line

Chen notation: entities are on top of the lines within relationships

Question 24

ER Schema

Answer 24

Practice