Databases Flashcards
1
Q
Characteristics of a database
A
Data is logically related (entities with attributes and relations to other entities)
Self-describing (schema/metadata)
Data abstraction (separation of internal and external representation)
2
Q
3-level way of representing a database management system
A
External level: the user’s view
Internal level: The technical part
Conceptual level: How the two interact
3
Q
Advantages and disadvantages of using a DBMS
A
Advantages: control over data redundancy, consistency, standards, improved accessibility, improved accessibility and productivity (can extract more information from the same amount of data), improved maintenance, scaling
Disadvantages: complexity, size, performance (sometimes worse than single-purpose systems), single point of failure
4
Q
How does the relational data model work
A
A relation is a table with rows and columns
A column is called an attribute (or field)
A row is called a record
Two tables are often related by a third table (e.g. Students and Courses are related by a StudentCourses table)
5
Q
What is Entity-Relationship Modelling?
A
A way of communicating databases graphically: non-technical, free of ambiguities
Examples include crow’s feet notation and UML
Specifies attributes, relationships, constraints
6
Q
What is normalisation?
A
The process of reducing data redundancy and minimising the number of attributes across all tables. This helps avoid update anomalies and saves space.
7
Q
Creating a table with SQL
A
CREATE TABLE Staff (staffNo VARCHAR(5), LName VARCHAR(15), salary DECIMAL(7,2));
8
Q
Inserting records into a table with SQL
A
INSERT INTO Staff VALUES (“SG16”, “Brown”, 8300);
9
Q
Querying a table with SQL
A
SELECT staffNo, LName, Salary
FROM Staff
WHERE salary > 10000;
10
Q
Purpose of three-level architecture
A
Realises data abstraction: Users can access same data but have different customised views
Users can change views without affecting other users
Internal structure can be changed without affecting users
11
Q
External level
A
Users’ view of the database
Multiple different views of the same data (e.g. 20 Jan ‘22 vs 2022-01-20)
Adapted to specific needs
Different entities, attributes, relationships
Possibly derived/calculated/combined (e.g. getting age from date of birth)
12
Q
Conceptual level
A
Community view of the database, shared by all users
Describes what data is stored (entities, attributes, relationships, constraints, security/integrity information)
13
Q
Internal level
A
Physical representation of the DB
Describes how the data is stored to achieve optimal runtime performance and storage utilisation
Interface to operating system
Compression and encryption
14
Q
DB schema
A
The description of the database, the result of the design process
Should not change
Multiple external subschemata (different views)
One conceptual schema (all entitie/attritutes/relationships)
One internal schema (low-level description, storage, indexes etc)
15
Q
DB instance
A
The data in the database at a particular point in time, changes whenever data is edited
16
Q
Logical data independence
A
External schemata remain the same if we change the logical structure on the conceptual level
17
Q
Physical data independence
A
Conceptual schema remains the same if we change the internal schema (data structures, algorithms)
18
Q
Conceptual design phase
A
Construct a first, high-level model of the data
Uses entity-relationship modelling
Identify the entities, attributes, relationships and constraints
Based on users’ requirements
Independent of any physical considerations
19
Q
Logical design phase
A
Construct the relational data model (the actual tables, e.g. Students, Courses, StudentCourses)
Normalisation takes place
20
Q
Physical design
A
Describe the implementation of the logical design (storage structures, access methods, security)
Optimise for performance
21
Q
Relation
A
Table with rows and columns (e.g. Student)
22
Q
Attribute
A
Named column in a table (e.g. surname)
Field
23
Q
Tuple
A
A row in the table (e.g. S002, Jane, Doe)
Record
24
Q
Domain of an attribute
A
Set of allowed values (e.g. positive integers, strings starting with capital letter)
25
Cell
Value of specific attribute in tuple
26
Degree
Number of attributes/columns
27
Cardinality
Number of tuples (rows)
28
When is a relation normalised?
Every cell has exactly one value, no repetition of identical tuples (rows)
29
NULL
Absence of a value
Represents an attribute value that is currently unknown or not applicable
Has to be handled in a special way
30
What is a key
A set of attributes whose values uniquely determine a tuple
Candidate key: Candidate Key is a collection of attributes or a single attribute (single column) that has the ability to uniquely identify records in the table (fully functionally determines all other attributes)
Primary key: Like a candidate key but there's only one of them
Alternate key: Any candidate key which isn't the primary key
Simple key: A key that consists of a single attribute
Composite key: A key that consists of multiple attributes
31
Foreign key
A set of attributes in a table that refers to the primary key of another table (e.g. mentorNo in Student)
32
Integrity constraints
Domain constraints: attribute values have to lie within their respective domain
Entity integrity: Attributes of the primary key cannot be NULL
Referential integrity: Foreign keys must match some primary key or be NULL
Prevents deleting a tuple if it's referenced somewhere else
33
What is a view?
A virtual relation (not stored physically)
Derived from one or more base relations
Computed upon request
Main use: showing customised information, with dynamic quantities (age from date of birth)
34
ER modelling vs Relational Data Model
ER modelling: Object-based, visual representation, used to understand data requirements, high-level and non-technical, intuitive
Relational Data Model: Record-based, mathematically sound, used to define database schema, low-level technical, not very intuitive
35
Entity in ER modelling
A thing that should be explicitly represented (e.g. a student, a course, a teacher), represented as a labelled rectangle
36
Relationships in ER modelling
A named association between two or more entity types (e.g. student _attends_ course, person _is a citizen of_ country, represented as a labelled line, triangle represents reading direction
37
Constraints in ER modelling
min..max
* = no constraint
"Teacher 1..1 is head of 0..1 School" means that a teacher can be head of 0-1 schools, and all schools must have 1 head
Cardinality of a..b c..d is b:d
1:1 = one-to-one, 1:* = one-to-many, *.* = many-to-many. a and c indicate participation
38
Attributes in ER modelling
Properties of an entity type
Represented as the lower part of an entity rectangle, primary key underlined
Names use camelCaseNotation
39
Types of attribute
Simple: one component
Composite: multiple components, indented
Single-valued: Only one value for each entity (e.g. name)
Multi-valued: multiple values for each entity (e.g. phone number), range given as [min..max]
Derived: Computed from other attributes (e.g. age), prefixed with "/"
40
How to turn an ER model into a Relational Data Model
1:1 relationship: Add foreign key to (either) mandatory entity, if both are mandatory then combine into one relation
1:* relationship: Add foreign key to "many" side (e.g. teacherId as foreign key in Course)
41
Handling many-to-many relationships (*:*)
Create separate relation with two foreign keys (e.g. Attendance relation containing studentId and courseId). Connect it with two one-to-many relationships.
Entity1 A:B action C:D Entity2 => Entity1 1:1 action C:D EntityLink A:B 1:1 Entity2
42
Steps for constructing an ER model
1. Identify entities and relationships from real world scenario
2. Draw entities and connect them
3. Add constraints
4. Resolve many-to-many relationships
5. Add attributes and define primary keys
43
Top-down vs Bottom-up approach to designing databases
Top-down: Entity-Relationship model
Start from data requirements, use a graphical description of the database, identify entities in the real world
Bottom-up: Start with examples of actual data in tables, analyse relationships between attributes and redesign the tables in a better way
44
What does it mean to redesign tables in a "better way"?
No redundancy: Wastes storage space, may lead to inconsistencies
45
Examples of data redundancy
Multi-valued attributes which need to be stored in separate rows
Dependencies (e.g. collegeAddress is dependent on collegeName)
46
Examples of anomalies
Insertion anomaly: New records need to duplicate existing data
Modification anomaly: Modifying only one value leads to inconsistencies
Deletion anomaly: Deleting one piece of data and losing other data in the process
47
Decomposing a table
Avoids modification anomalies
Student(id, forename, surname, collegeName, collegeAddress) => Student(id, forename, surname, college) + College(college, collegeAddress)
college in Student is a foreign key
48
Functional dependencies
If A and B are two sets of attributes in a relation, and each value of A is associated with exactly one value of B, then B is functionally dependent on A and A determines B (A => B)
Informally this means that if you know the value(s) of A then you know the value(s) of B, and A is the determinant of B
49
Types of functional dependency
Full functional dependency: B is functionally dependent on A and not on any smaller subset of A
Partial functional dependency: B is functionally dependent on A and at least one smaller subset of A
Transitive functional dependency: If A => B and B => C then C is transitively dependent on A via B
50
Closure of a set of dependencies
Given a set of functional dependencies F, its closure F+ is all the functional dependencies that are implied by the dependencies in F
51
Armstrong's axioms for computing F+ from F
Reflexivity: if B is a subset of A, then A => B
(id, forename, surname) => (forename, surname)
Augmentation: if A => B, then A+C => B+C
(id) => (college)
therefore
(id, forename) => (college, forename)
Transitivity: if A => B and B => C, then A => C
(id) => (college) and (college) => (collegeAddress)
therefore
(id) => (collegeAddress)
(De)composition: if A => B,C then A => B and A => C and vice versa
Composition: if A=>B and C=>D, then A,C => B,D
52
Finding the closure F+ of F
Start with all dependencies in F
Repeatedly apply Armstrong's axioms
Add any new dependencies discovered along the way
53
1st Normal Form
No repeating groups: No attributes or groups of attributes with multiple values for a single occurrence of the primary key (i.e. multi-valued variable)
If there is a fixed number of repetitions, you can use separate attributes (e.g. phoneType + phoneNumber => mobilePhone + workPhone)
54
General solution for converting to 1st normal form
Flattening: Fill empty cells with repeating values
Create separate relation
55
2nd Normal Form (only relevant for relations with composite keys)
A table is in 2nd Normal Form if it's in 1NF and has no partial functional dependencies: every non-key attribute is dependent on the whole primary key (no partially dependent attributes)
56
Converting to 2nd normal form
Remove the partially dependent attributes and put then in a new relation along with a copy of their determinant
57
3rd Normal Form
No transitive dependencies, meaning every dependency must be fully/directly dependent on the primary key(s)
58
Converting to 3rd Normal Form
Remove the transitively dependent attributes and put them in a new relation along with a copy of their determinant
59
Two components of SQL
Data Definition Language: Defines the schema for each relation, defines the domain of each attribute, specifies integrity constraints (e.g. primary/foreign keys)
Data Manipulation Language: Inserts/updates/deletes/retrieves data from the database. The query language is the part of the DML that involves retrieval
60
SELECT Statement syntax
SELECT (DISTINCT)
- FROM
-
Key Links
-
Subjects
-
Company
-
Find Us
-
