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 1NF?

Every attribute in that relation is single-valued attribute

What is 2NF?

Every non-primary-key attribute is fully functionally dependent on the primary key

What is 3NF?

No transitive dependency for non-prime attributes

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

Each column must have a specified type, such as numeric, string, date/time or binary. Choosing the correct data type is critical for performance, storage efficiency, and data integrity

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

What is Domain Integrity?

A set of database rules ensuring data accuracy, validity, and consistency within a specific column

Why cant an INT variable store ‘Hi’?

Domain integrity

What does Domain Integrity restrict?

Input values (to allowed types, formats, or ranges)

Why is Domain Integrity useful?

It acts as a primary defence against invalid data entry (maintaining reliable database functionality)

What are the requirements for Entity Integrity? Every entity must have a:

Primary Key

True/False primary keys must be NOT NULL

True

What does Referential Integrity do?

Ensures data consistency by guaranteeing that relationships between tables remain valid

What is an example needing Referential Integrity?

Assigning an order to a non-existent customer

What is User Defined Integrity?

A set of customised rules, constraints, or validation criteria

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

T/F a transaction is a LUW

True

What are the steps (keywords) of transactions in SQL?

START TRANSACTION, COMMIT, ROLLBACK

What does START TRANSACTION indicate in SQL?

The entry point of a LUW

What does COMMIT indicate in SQL?

The end point and makes changes in LUW permanent and logged

What does ROLLBACK indicate in SQL?

Undoes all committed changes in LUW back to the START

What is the end result of ROLLBACK in SQL?

No change - like the transaction never started

What does concurrency control allow?

Multiple transactions or processes to run simultaneously without interfering with each other

What potential problems does concurrency control solve? (4)

Lost update, uncommitted dependency, inconsistent analysis, deadlock

What is the lost update problem?

When two or more transactions select the same record, read it, and then update it simultaneously one transaction overwrites the other and the first update is lost

What is the uncommitted dependency problem?

When a transaction reads data that is later modified by another transaction before the first transaction commits

What is the inconsistent analysis problem?

Where one transaction reads data while another modifies it

What is the deadlock problem?

When both transactions keep waiting on each other to finish

Why is security of data important?

Data is valuable and private, and unauthorised users can damage database integrity

What is a threat (according to that book)?

Any situation or event, whether intentional or accidental, that may adversely affect a system and consequently the organisation

Name 3 types of security

Data, network, physical

What are the worst causes of bad security?

Weak authentication, poor database configuration, SQL injection

How can SQL injection be prevented?

Prepared statement, input sanitisation,

What do prepared statements do?

Separates the query structure (the SQL) from the actual data (user input)

What does the input WHERE UserID = 456 OR 1=1 look up when prepared statements are used?

UserID that is ‘456 OR 1=1’

What does the input WHERE UserID = 456 OR 1=1 look up when prepared statements are NOT used?

Nothing as 1=1 is always true

What is input sanitisation?

The security process of cleaning, filtering, or modifying user input to ensure only valid characters are input

What is the definition of a distributed database?

A logically inter-related collection of shared data (and a description of this data), which is physically distributed over a computer network

What is the definition of a DDBMS (Distributed Database Management System)?

A software system which permits the management of the distributed database and makes the distribution transparent to users

Fill in the blanks:

Data is split into ………(1)
………(1) may be ……….(2)
Fragments are allocated to ……(3)
….. (3) are linked by a communications …….(4)

fragments, replicated, sites, network

What is horizontal fragmentation?

Splitting tables up by records (rows)

What is vertical fragmentation?

Splitting up tables by attributes (columns)

What is hybrid fragmentation?

Splitting up tables by records and attributes

What is a centralised database?

A single database and DBMS stored at one site with users distributed across the network

What are the two main types of distributed database?

Partitioned, complete

What is partitioned replication in a distributed database?

Database is partitioned into disjoint fragments; each fragment is assigned to one site

What is complete replication in a distributed database?

A complete copy of the database is maintained at each site

What is selective replication in a distributed database?

A combination of partitioning, replication and centralisation

Centralised comparison: Locality of reference, Reliability & Availability, Performance, Storage Costs, Communication Costs

Lowest, Lowest, Unsatisfactory, Lowest, Highest

Partitioned comparison: Locality of reference, Reliability & Availability, Performance, Storage Costs, Communication Costs

High, Low for item; high for system, Satisfactory, Low, Low

Complete replication comparison: Locality of reference, Reliability & Availability, Performance, Storage Costs, Communication Costs

Highest, Highest, Best for read, Highest, High for update; low for read

Selective replication comparison: Locality of reference, Reliability & Availability, Performance, Storage Costs, Communication Costs

High, OK for item; high for system, Satisfactory, Average, Low