1.3.2) Databases Flashcards
What is an entity?
An item of interest about which information is stored
What is a relational database?
A database which recognises the differences between entities by creating tables for each entity.
What is an attribute?
The characteristics of an entity
What is a flat file?
A database that consists of a single file
What is a primary key?
A unique identifier for each record in the table (eg. an ID number)
What is a foreign key?
The attribute which links two tables together (eg. Tom plays for Man City, Tom is the primary key and Man City is the foreign key)
What is a Secondary Key?
A key that allows the database to be searched quickly. (eg surname/email)
What is a one to one relationship?
An entity that can only be linked to one other entity (eg husband and wife)
What is a one to many relationship?
An entity that can be linked to multiple different entities (eg mother to kids)
What is many to many?
one entity can be associated with many other entities and the same applies the other way round (eg courses and students)
What is normalisation?
The process of coming up with the best possible layout for a relational database
what does normalisation try to accomplish? (4)
Unnecessary duplicates, consistent data throughout linked tables, records can be added and removed without issues, complex queries can be carried out
What are the rules for first normal form? (3)
- Each row is unique.
- No cell has more than one value.
- There are no repeating groups.
What is the rule for second normal form?
All data must depend on the primary key
What are the rules for third normal form?
The primary key must fully define all non-key columns, and non-key columns must not depend on any other key
What is indexing?
A method used to store the position of each record ordered by a certain attribute
what are some examples of capturing data methods?
Manual, magnetic ink character recognition, optical mark recognition, optical character recognition
What does SQL stand for?
Structured query language
What is SQL?
A declarative language used to manipulate databases
how would you select the name of a student called Bob from an SQL file called students?
SELECT studentname FROM students WHERE studentname= “Bob”
How would you order an SQL file?
ORDER BY attribute
How how do you use the join function in SQL?
SELECT file1.att, file2.att FROM file1 JOIN file 2 ON att1=att2
What is a One to One relationship?
An entity only linked to one other entity (eg husband and wife)
What is a one to many relationship?
One entity that can be associated with many other tables (eg a doctor having multiple patients)
What is a many to many relationship?
One entity can be associated with many other entities and the same applies the other way round (eg students and courses)
What is normalisation?
The process of coming up with the best possible layout for a relational database.
What does normalisation attempt to accomplish? (4)
- no redundancy (unnecessary duplicates)
- consistent data throughout linked tables
- records that can be added and removed without issues
- complex queries can be carried out
What are the rules for First Normal Form?
- A cell must never contain more than one value
- Each row must be unique (every row has a unique identifier)
- Each column name must be unique
- There must be no repeating groups (these should be moved into a new table)
What are the rules for 2NF?
All data must depend on the primary key
What are the rules for 3NF?
The primary key must fully define all non-key columns and non-key columns must not depend on any other key
What is Indexing?
a method used to store the position of each record ordered by a certain attribute
What does the CREATE function do?
Allow you to make new databases
what details must be specified when using the create function?
Whether it is the primary key, it’s data to type, whether it must be filled in
what does the alter function do?
Add, delete or modify the columns in the table
What is referential integrity?
The process of ensuring consistency and ensuring necessary data isn’t deleted
