1. 3. 2 Databases

Question 1

Relational Database Flat File

Answer 1

• Entity item of interest of which information is stored
• Relational database recognises differences between entities by creating different tables for each entity
• View Doctor Patient example in photos
• DoctorID is the attribute linking two tables together
• Attributes are characteristics of the entity, categories about which data is collected
• Flat file database consists of single file based around single entity and its attributes
• Written out as Entity1(Attribute1, Attribute2, Attribute3 …)
• Look at example in photos

Question 2

Keys

Answer 2

• Primary Key- A field that has a unique identifier for each record in the table. In Car photo unique identifier is the CardID as it is always different for each row, in Doctor photo PK is DoctorID and for patient table it would be PatientID, underline to show PK
• Foreign Key- Attribute that links two tables together, exists in one table as PK acts as FK in other. In Doctor photo DoctorID FK in Patient table, FK shown as asterisk
• Secondary Key- Allows database to be searched quickly, in Doctor photo patient unlikely remember PatientID but will know surname, secondary index (SK) set up on surname attribute. Makes it possible order / search by surname easier find specific patient
• Composite Key- Combination two or more columns in table can be used uniquely to identify each row in table E.g., CK photo Name and Age used to identify Roll_No, Arya 21 Roll 1 Used to avoid repeats in data

Question 3

Normalisation

Answer 3

• Definition-Process coming up best possible layout for a relational database
• Aim- No redundancy (duplicates), Consistent data throughout linked tables, Records can be added removed without issues, Complex queries can be carried out
• Three types First Second and Third Normal
• First Normal Form- There must be no attribute that contains more than a single value
• Second Normal Form- Database with no partial dependencies, already in FNF, no attributes can depend on part of a composite key
• Third Normal Form- In SNF and attribute only depends on the value of the primary key and nothing else

Question 4

Indexing and SQL

Answer 4

• Method used to store the position of each record ordered by a certain attribute, used to look up and access data quickly
• PK is auto indexed, almost never queried since it is not normally remembered
• This is why SKs are used, indexed to make the table easier and faster to search through on those particular attributes
• Standard Query Language, declarative lang manipulate databases
• SELECT, FROM, WHERE, ORDER BY… DESC ASEC, JOIN, CREATE, ALTER, INSERT INTO, UPDATE, DELETE

Question 5

Referential Integrity

Answer 5

• Process of ensuring consistency, ensures info not removed if required elsewhere in linked database.
• If two database tables are linked, one of these tables cannot be deleted as the other table requires its contents.

Question 6

Record Locking

Answer 6

• Process of preventing simultaneous access to records in a database.
• Used in order to prevent inconsistencies or a loss of updates.
• While one person is editing a record, this ‘locks’ the record so prevents others from accessing the same record.

Question 7

Redundancy

Answer 7

• Info important cannot be lost.
• Redundancy process of having one or more copies of the data in physically different locations.
• Damage to a copy, others unaffected and can be recovered.

Question 8

ACID

Answer 8

• Atomicity- A transaction must be processed in its entirety or not at all (£10 transfer £10 given)
• Consistency- A transaction must maintain the referential integrity rules between linked tables
• Isolation- Simultaneous executions of transactions should lead to the same result as if they were executed one after the other (Two people transfer money do not affect each other)
• Durability - Once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as in the event of a power cut (£10 transferred £10 gone)