CS22001 - Database Systems

How can you show a model of a relational database?

An ERD (entity relationship diagram)

What is a general rule for determining entities?

Look for nouns

What is a general rule for determining attributes?

Find info that each entity would contain

What is a general rule for determining relationships?

Look for verbs

What are the steps for creating a database?

Requirements, Conceptual Design, Logical Design, Physical Design, Verification, Maintenance

Which step does an ERD fall under?

Conceptual Design

What is a weak entity?

An entity that would not exist without another

Which entity is a weak entity?

Contact Info

What is a candidate key?

A minimal set of attributes which uniquely identifies each record

What is a composite key?

A candidate key which consist of two or more attributes

What is a primary key?

A candidate key which is selected to uniquely identify each record

What makes a good primary key?

One attribute, a numerical attribute, named sensibly

What is a foreign key?

When one table has the primary key

What is the purpose of a foreign key?

Allows tables to be linked without physically storing the relationship

What is a requirement of a joining table?

Both entities have many to many relationships

What is the purpose of a joining table?

To model many-to-many relationships by breaking them into two one-to-many relationships

When does an insertion anomaly occur?

Specific data cannot be added to a table without the presence of unrelated or incomplete data

When does an update anomaly occur?

When data is duplicated (redundant) across multiple records, and updating only some - but not all - of those records leaves the database in an inconsistent state

When does a deletion anomaly occur?

When deleting a record from a database unintentionally removes other crucial, unrelated data, causing loss of information

What is CRUD?

Create, read, update, delete`

Create in SQL (2)

CREATE

INSERT INTO

Read in SQL

SELECT

Update in SQL

UPDATE

Delete in SQL

DELETE

Example insert in SQL

INSERT INTO Staff (Name, Address, Phone) VALUES ('Daniel Rough', 'Room 2.07', '384145')

Example update in SQL

UPDATE Staff SET Address = 'Room 2.05’, 	 Phone ='123456' WHERE Name ='Daniel Rough'

Example delete in SQL

DELETE FROM Staff WHERE Name ='Daniel Rough'

Select statement format in SQL

SELECT columns
	FROM TableName
	WHERE condition

Keywords SQL (6)

SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT

SELECT Aggregates in SQL

COUNT, SUM, AVG, MIN, MAX

What kind of join is this?

INNER

What kind of join is this?

FULL OUTER

What kind of join is this?

LEFT OUTER

What kind of join is this?

RIGHT OUTER

What is a view?

The dynamic result of one or more relational operations operating on the base relations to produce another relation

True/False views are read only

False (can be read only or updateable)

Why are views useful? (2)

To hide complexity and increase security

What does a horizontal view do?

Restricts the rows (records) of a table

What does a vertical view do?

Restricts the columns (attributes) of a table

Digression ????????

?????????

What CRUD for vertical view?

CRUD

What CRUD for horizontal view?

RU

What is a transaction?

A sequence of one or more SQL operations treated as a single, atomic logical unit of work

What are the two possible outcomes of a transaction?

Commit, rollback

What is integrity in databases?

Accuracy and reliability of the data

Domain Integrity ?

?

Entity Integrity ?

?

Referential Integrity?

?

User Defined Integrity ?

Rules ?

What does ACID stand for?

Atomicity, Consistency, Isolation, Durability

What is atomicity?

Either all the operations in a transaction are successful, or none of them are

What is consistency?

A transaction always leaves the database in a consistent state - no integrity constraints are violated and the new updated records are immediately available

What is isolation?

Transactions should execute as if there are no other concurrently running transactions

What is durability?

Once a transaction has committed, it is not lost due to any subsequent failure

What does LUW stand for?

a Logical/Least Unit of Work

What does LUW mean?

Either everything is completed or nothing is

slide 44/66 week4