Databases Flashcards
Entity-Relationship Modelling
A method of abstractly describing the data tables and the relationships between them visually. They can be used to reduce redundancy and construct a relational database.
What is an entity?
It is a thing about which data is to be stored, for example: a customer.
What is an attribute?
Attributes are characteristics or other information about entities, for example: the customer’s name or address.
What is a relational database?
It is a type of database that stores and provides access to data points that are related to one another.
What is a primary key?
A primary key is an attribute that provides an unique identifies for every entity in a database table.
What is a composite key?
If it is not possible to form a primary key from just one attribute. It is possible to combine attributes to form what is called a composite primary key
What is a foreign key?
A foreign key is an attribute in a table which is the primary key in another, related, table.
What is a one-to-one relationship?
Where one entity could be linked to only one of another entity
What is a one-to-many relationship?
Where one field from a table is linked to several records from another table.
What is a many-to-many relationship?
For example, a tutor might have multiple students and students might have multiple tutors. This type of relationship can become complicated when turned into a database because they cannot occur in real life; data cannot be transferred as it logically doesn’t make sense. This can be fixed using an association table. For example, a tutor can publish many papers and a paper can have many publishers so the association is Publication Issue Number; this is because it’s the simplest link.
What is normalisation?
Normalisation is the process of removing redundancy within a database. Doing so makes accessing and storing data easier and more efficient
Steps for Un-normalised Form
- Select an initial key. This acts as a starter key. It needs to be unique so you can derive other values from it. If there is no suitable key, add one.
- Transfer all identifiable attributes, ensuring each has their own relevant and unique name.
- Look for repeating groups. These are a group of similar attributes that have multiple values for a single value of the initial key. Select a suitable initial key for the repeating group, surround them with brackets, and write them apart from the single-valued attributes
Steps for First Normal Form
- Create new relations by separating all repeating groups select a new primary key for the new relation and propagate the initial key to form a new composite key.
- All other single-values attributes remain with the initial key
Steps for Second Normal Form
The second stage of normalisation requires that all key attributes in a table are dependent on each other and that the table is in first normal form. Only then can you move on to creating a database that is in second normal form which will provide even more strength to the data structure.
- Separate any attributes from keys formed in the previous step that are only dependent on one part of the composite key
Steps for Third Normal Form
The third step is the transition into third normal form.
- Separate any attributes that are dependent on other non-key attributes; foreign keys are retained in the original relation
- Check composite keys for redundant parts. If a part of a key can be derived from other attributes, demote key attribute to non-key
Advantages of normalisation
no redundancy, consistent data throughout linked tables, records can be added and removed without issues, complex queries can be carried out
What is SQL?
Structured Query Language is a declarative language used for querying and updating tables in a relational database.
What are the two types of SQL?
Data Definition Language, Data Manipulation Language
What is DDL?
The data definition language is a language used to build the structure of a database, and consists of a variety of commands which allow the user to define the structure of the database by creating, altering or deleting tables. It also allowed the administrator to apply constraints to a database table.
What is DML?
The data manipulation language is the language used to populate and update the database after it has been created. DML allows the user to insert, modify, delete, and query a database table for the data values of the entire table, for a specific record or for records that meet criteria.
VARCHAR(n)
Variable length string of maximum n characters
CHAR(n)
Fixed length string of n characters
DATE
A date (there is also TIME, TIMESTAMP, and DATETIME)
INT
Whole numbers (positive and negative)
BOOLEAN
TRUE/FALSE
DECIMAL (n,d)
Fixed-point decimal number with n digits in total, d is the number of digits after the decimal point
DOUBLE (n,d)
Floating-point decimal number with n digits in total, d is the number of digits after the decimal point
ENUM
List of permitted values (encoded as numbers)
BLOB
Binary data (such as an image)
TEXT
For large quantities of text
How do you create a table?
CREATE TABLE tablename(
Entity1 DATATYPE(n),)
How do you define a primary key?
PRIMARY KEY (MemberId, CourseCode)
How do you define a foreign key?
FOREIGN KEY (name) REFERNCES
How do you alter a table structure?
Used to add, delete or modify columns in an existing table:
ALTER TABLE tablename
ADD nnnn INTEGER
Delete or alter a table structure
To delete a column
ALTER TABLE tablename
DROP mmmm
TO change the data type of a column:
ALTER TABLE table name
MODIFY COLUMN n DATAYPE(n) NOT NULL
How do you SELECT FROM WHERE ORDER BY
SELECT productID, productName, subject, price
FROM tblProduct
WHERE level = 4
ORDER BY productNAME
Updating data
UPDATE Product
SET Description = “mmlMK”, Price = “njnomom”
WHERE ProductID = “A345”
Deleting
DELETE FROM Product
WHERE ProductID = “A345”
What are client server databases?
The client can be a node on a network, an individual computer or someone sat on the opposite site of the planet and the structure still works the same. the idea is that a client requests data from another program (the server) by a connection made over a wide area network or over a local area network. Once the data request is fulfilled, the connection is terminated.
Advantages of client-server
Centralisation - allows server admins to decide the inf
