(4.10) Fundamentals of databases

Question 1

What is an entity?

Answer 1

An entity is something about which data is stored

Question 2

What is an attribute?

Answer 2

Characteristics or other info about entities

Question 3

What is an entity identifier?

Answer 3

• In a database, each entity requires a unique identifier
• an entity id is an attribute for an entity, which is unique within that table
• sometimes entity ids are multiple attributes for uniqueness

Question 4

What is an entity description?

Answer 4

• An entity description describes how data will be stored about entities
• Underlining shows the attribute/s that form the entity identifier

ex.
Contacts (Name, PhoneNumber, Email, Age, etc..)

Question 5

Relational databases: how are tables linked together?

Answer 5

Relational databases link tables together using common attributes (relational keys), there are 3 types of relationship:
* one-to-one
* one-to-many
* many-to-many

Question 6

Entity relationship diagrams

Answer 6

• entity relationship diagrams show the relationships between tables
• one-to-one : ——
• one-to-many : —–<
• many-to-many : >—–<

Question 7

Primary and foreign keys: what are they?

Answer 7

A Primary key is an attribute that is a unique identifier for every entity in a table, e.g. an IDNumber

when tables are linked by attributes, the attribute must be a primary key in one table and a foreign key in the other

A Foreign key is an attribute in a table which is a primary key in another, related, table

attributes can be combined to form a composite primary key

Question 8

What has to be created when linking a many-to-many relationship?

Answer 8

When linking a many-to-many relationship you have to create a new link table

Question 9

Why are databases normalised and how?

Answer 9

Databases are normalised so that they can be efficient while keeping the data accurate and reliable:
* Allows for faster searching and sorting
* They are easier to maintain
* Duplication of data is lower and higher data consistency
* reduces the number of update, insertion and deletion anomalies

Normalising involves ensuring entities have no repeated or redundant data

Question 10

Databases: what is first normal form?

Answer 10

• Doesn’t contain any repeating attributes
• Has atomic data (no column has more than one value)
  ex. phone number is atomic, whereas Firstname, Lastname is not

Question 11

Databases: what is second normal form?

Answer 11

• All of first normal form (No repeating attributes, atomic data)
• Has no partial key dependencies
  (this is when a non-key attribute doesn’t depend on the whole of the composite key)

E.g. a table called CourseEnrollment:

StudentID, CourseID, StudentName, CourseName

• StudentName depends only on StudentID
• CourseName depends only on CourseID

How to fix this:
Student(StudentID, StudentName)

Course(CourseID, CourseName)

Enrollment(StudentID, CourseID)

Question 12

Databases: what is third normal form?

Answer 12

• All of first normal form (No repeating attributes, atomic data)
• All of second normal form (partial key dependencies removed )
• Has no non-key dependencies

(All non-key attributes depend on the key, the whole key and nothing but the key)

Question 13

Client server databases: what do they do?

Answer 13

• Client server databases provide simultaneous access to a database for multiple users
• When different users try to access the same field at the same time, concurrent access occurs

Question 14

What is concurrent access?

Answer 14

Concurrent access is the problem of
updates being lost if two clients edit a record at
the same time

Question 15

How can concurrent access be fixed?

Answer 15

Concurrent access can be managed
by:

• record locks - while one user is using a record, it is locked to other users
• serialisation - requests to edit a record are queued
• timestamp ordering - each command is assigned a timestamp, marking when it was initiated
• commitment ordering - an algorithm is used to work out an optimum order, taking into account the impact of the command on the rest of the database

Question 16

What is a declarative language?

Answer 16

The programmer describes the result that is required rather than the process that should be followed

Question 17

SQL: SELECT

Answer 17

• Used to retrieve data

SELECT < attribute > FROM < table > WHERE < condition > ORDER BY < ASC/DESC >

(order by is optional)

Question 18

SQL: UPDATE

Answer 18

• Used to modify the attributes of an existing entity

UPDATE < table > SET < attribute > = < value > WHERE < attribute > = < value >

Question 19

SQL: DELETE

Answer 19

• Used to remove entities

DELETE FROM < table > WHERE < condition >

Question 20

SQL: INSERT

Answer 20

• Used to add new records to an existing table

INSERT INTO < table > (< column1 >, < column2 >, …) VALUES (< value1 >, < value2 >, …)

but can be simplified to

INSERT INTO < table > VALUES (< value1 >, < value2 >, …)

when all of the columns in the table are being used in the correct order.

Question 21

SQL: Wildcards

Answer 21

• Can be used in SQL to specify any possible value
  (*)

Question 22

Defining a table with SQL

Answer 22

• The CREATE command allows new tables to be made within a database
• It specifies: the name, its attributes, their data types
• Entity identifiers are also specified (primary & secondary keys)

Question 23

SQL data types (for defining tables)

Answer 23

• Fixed length string - CHAR(size)
• Variable length string - VARSIZE(size)
• Integer - INT(size)
• Number with fractional part - FLOAT(size, precision)
• Date - DATE
• Date and time - DATETIME
• Time - TIME
• Year - YEAR