Topic 10 - Fundamentals Of Databses Flashcards
Explain the concept of a relational database
more than one related table
normalised so no data is repeated
How are tables linked together in databases
one to many
using a crows foot
Define what an attribute is.
a characteristic of the data in the column
Define what a primary key is
ensures that every piece of data is unique
e.g. if two people have the same name they will have a different id.
the id is the primary key
Define what a composite key is
A combination of two or more columns in a table to act as a primary key
Define what a foreign key is
A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables.
Understand why databases are normalised
in order to remove any unnecessary or repeating pieces of data.
- Saves memory space
- Data is easier to change (don’t have to go to more than one location to change it)
- more efficient, no wastage
How do you normalise relations to third normal form
get rid of any groups with multiple pieces of data in one field (e.g. multiple subjects)
seperate atomic attributes (name into firstname lastname)
identfy a primary key
split partial dependencies into different tables. (course name and teacher name will depend on course id so we can split these up into a new table)
- only do this if the dependency (course id) is part of your composite key
change many to many relationships into 2 different one to many relationships in a new table (e.g. student id and course id)
REMEMBER: “ALL ATTRIBUTES ARE DEPENDANT ON THE KEY, THE WHOLE KEY, AND NOTHING BUT THE KEY
make sure no data is repeating
How do you select data from a database,
if we wanted to select the dob regarding ‘James Chadwick’
SELECT dob
FROM students
WHERE firstName =’James’ and lastName =’Chadwick’
How do you update data in a database,
if we wanted to change james chadwick subject to computing instead of maths. Table name is students
UPDATE Students
SET subject = “computing”
WHERE firstName = ‘James’, lastName = ‘Chadwick’
How do you insert data into a database,
if we wanted to insert a person called james chadwick into student table
INSERT
INTO students
VALUES (‘James,’Chadwick’)
How do you delete data from a database,
if we wanted to delete a person called james from student table
DELETE
FROM names
WHERE firstName = ‘James’
How do you use SQL to create a database table named Students with studentid, first and last name and dob
CREATE TABLE “Students” (
“StudentID” INT PRIMARY KEY
“FirstName” VARCHAR(30)
“LastName” VARCHAR(50)
“DOB” VARCHAR(10)
);
How does a client server database system provide simultaneous access to the database for multiple clients.
Transaction processing -
Any information processing which is an individual operation. Each operation must completely succeed or it will fail.
How can concurrent (multiple) access be controlled in a database
ACID RULES
Atomicity - a change to a database must be completely performed or it won’t be saved to the database
Consistency - Any change in the database must retain the overall state of the database
Isolation - Change in database cannot be interrupted by another change in database. Done using record locking, where it locks the records or fields of data concerned until change is completed.
Durability - Once a change has been made to a database it must not be lost due to a system failure
Done by writing changes into secondary storage as soon as change is made
