Databases Flashcards
what is a database?
They are structured sets of data. They are organised in such a way that they can be easily managed, accessed, updated and analysed.
Eg. a relational database - made up of tables
what is a flat file?
The simplest form of a database - it is a single file, normally organised in a table structure with rows and columns
how would you write an entity description?
Entity1 (Attribute1, Attribute2, … )
what is a relational database?
a set of tables whose records are linked by certain fields. Each table contains data on one entity. the tables can be linked together using foreign keys (a relationship)
what is the purpose of an entity identifier?
it is used to uniquely identify the entity - it is the primary key in a relational database
What is a record?
All the fields relevant to an entity - kind of like a row in a table
what is an entity?
it is a category of object, person, event or thing of interest to an organisation, about which data is to be recorded
what is a field/attribute?
it is a property or characteristic of an entity - it is kind of like a column in a table
what is the primary key?
- it is an entity identifier that uniquely identifies records in an entity. Data in the entity will be sorted by this by default
- denoted by being underlined in the entity description: eg. Dentist (DentistID, Title, Firstname, Surname, Qualification)
what is a composite primary key?
- its a primary key made up of multiple fields - used to maintain uniqueness
- eg. a customer order with multiple order lines would have OrderNumber + OrderLine as composite primary key
what is a secondary key?
- any other fields in the table that aren’t the primary key
- makes sure that records in the database remain searchable since a customer/user may not know the relevant primary key value
what is a foreign key?
- its an attribute that creates a join between tables
- the attribute is common to both tables
- foreign key in one table = primary key in the table its linked to
- denoted by being in italics - eg. Patient (PatientID, Title, Firstname, Surname, Address, Telephone, DentistID)
How can entities be related + how are they represented in an entity relationship diagram?
One-to-one:
- eg. husband + wife, country + prime minister
- shown by 2 rectangles (for entities) joined by one straight line
One-to-many:
- eg. school + pupils, mother + child
- shown by 2 rectangles joined by a straight line on one side and birds legs on the other
Many-to-many:
- avoided where possible (eg, through normalisation)
- eg. actors + films, products + components, student + course
- represented by 2 rectangles joined by birds legs on either end
what is an entity relationship diagram?
it is a diagramatic way of representing the relationships between entities in a database
how would you remove a many-to-many relationship?
eg. you have 2 tables:
- student (studentID, name, address)
- course (courseID, subject, level)
create a middleman table eg:
- student (studentID, name, address)
- enrolment (_studentID_, _courseID_)
- course (courseID, subject, level)
what is referential integrity?
no foreign key in one table can reference a non-existent record in another table - eg. can’t add a subscription for a customer if the customer ID doesnt exist in a record
what is relational database structure?
- tables in the database = relations
- one database will have one or more relations
- relation has rows - aka records
- each record has fields/attributes - like columns
how are databases indexed?
- records automatically indexed by primary key
- can also be indexed by any other secondary key (secondary index)
what is normalisation?
- it is the process used to come up with the best possible database design
- 3 stages: 1st normal form (1NF), 2nd normal form (2NF), 3rd normal form (3NF)
what should a normalised database look like?
- no data is unnecessarily duplicated
- data is consistent throughout the database
- table structure should be flexible enough for you to enter as many or as few items as required
- structure should allow as complex queries to be made
what are the properties required of a database in first normal form?
- no repeating attributes/groups of attributes
- must have a primary key
- ## atomic - only one data item in one field
