Lecture 2 Flashcards
Relational DBMS
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
ACID
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
A transaction
A unit of work that is executed as a single entity.
Relational approach to a database (Reminder)
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.
Entity
Something of interest to the use of the database. e.g Customer, Student, Geolocation
Cell
An individual piece of data stored in a table.
Field
A property of an entity. (column of a table)
Row / Record
An instance of an entity, a collection of cells representing an individual entity.
Result-set
A non-persistent table, the result of a query (What you’ll be working with in code)
Primary Key
One or more columns that together can be used as a unique identifier for each row in a table.
Foreign Key
One or more columns that together are used to identify a record in another table.
Relation integrity constraints (3 constraints)
- Domain constraints
- Key constraints
- Referential Integrity Constraints
Domain constraints for Relational Models
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
Key constraints for Relational Models
Primary key attributes must be unique for different records in the relation.
Primary Key
- 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)
);
Candidate Key
- 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.
Super Key
Consists of candidate keys attributes + extra attributes
Foreign Key
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);
Simple and Composite Keys
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.
Defining behaviour on FK’s:
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,
Defining behaviour on FK’s:
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,
Entity Relationship Model
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.
Entity Relationship Model entities:
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.
ER diagrams:
Entity - rectangle
Relationship - diamond
Attribute - circle
Key attribute - circle with line
Chen notation: entities are on top of the lines within relationships
ER Schema
Practice
