8.1 Database Concepts

# File Based System

- Data stored in discrete files, stored on computer, and can be accessed, altered or removed by the user
## Limitations of File Based System
- Flat-file has more data redundancy because the same data is stored many times
- There is program-data dependence with flat-files, because any changes to the structure of the data means the programs that access that data have to be re-written
- Flat-file has more data inconsistency (worse data integrity) because when data is updated in one place, it is not updated everywhere
- It is not easy to perform complex queries (searches) because a new program has to be written each time
- Flat files could have a lack of privacy as user views cannot easily be implemented
## The features of a relational database that address the limitations of a file-based approach
- Reduced data redundancy
	- because each item of data is only stored once
- Improves data integrity
	- changes in one table will automatically update in another
	- linked data cannot be entered differently in two tables
- Program-data independence
	- changes to the data do not require programs to be re-written
- Complex queries are easier to run
- Can provide different views
	- so users can only see specific aspects of the database
# Relational Database Modelling
- **Entity:** An object that data is stored about.
- **Table:** Contains a group of related entities in rows and columns called an entity set
- **Feild:** A column/attribute in a table
- **Tuple:** A row of data in a table about one instance of an object
- **Attribute:** A field or column in a relational database
- **Primary key:** attribute or combination of them that uniquely define each tuple in relation
- **Candidate key**: An attribute / field (or set of attributes / fields) that **could** be a primary key
- **Foreign key:** A field in one table that is linked to a primary key in another table
- **Referential integrity:** prevents users or applications from entering inconsistent data
- **Secondary key:** a candidate key that has not been chosen as the primary key(An additional/alternative key used as well as the primary key to locate specific data)
- **Indexing:** creating a secondary key on an attribute to provide fast access when searching on that attribute; indexing data must be updated when table data change
#### Advantages
- Reduced data redundancy, because each item of data is only stored once
- Maintains data consistency (improves data integrity), changes in one table will automatically update in another
- Program-data independence, changes to the data do not require programs to be re-written
- Complex queries are easier to run
- Can provide different views, so users can only see specific aspects of the database
##### what is meant by data integrity and give one example of how this is implemented in a database.
- Methods of making sure the data is consistent
- Enforcing referential integrity
- If data in one table is deleted/edited all tables are updated // cascading update/delete
- Validation/verification rules
#### Referential integrity
- makes sure data is consistent
- makes sure all data is up-to-date
- ensures that every foreign key has a corresponding primary key
- prevents records from being added / deleted / modified incorrectly
- makes sure that if data is changed in one place the change is reflected in all related records
- makes sure any queries return accurate and complete results
## Normalization
##### First Normal Form (1NF)
There are no repeating groups of attributes.
No repeating groups or repeating attributes. Intersection of each tuple and attribute contains only 1 value.
![image-20231214115746766.png](https://doc.tiegg.com/media/202411/dc5961a965784d9dbe98d5082b394b084372.png)

##### Second Normal Form (2NF)

There are no partial dependencies.

it is in 1NF and every non-primary key attribute is fully dependent on the primary; all the incomplete dependencies have been removed. Example:
![image-20231214115817860.png](https://doc.tiegg.com/media/202411/656ad51131a641af902aee26dcc8dc943889.png)
##### Third Normal Form (3NF)
All fields are fully dependent on the primary key.
it is in 1NF and 2NF and all non-key elements are fully dependent on the primary key. No inter-dependencies between attributes.
- MANY-TO-MANY functions cannot be directly normalized to 3NF, must use a 2 step process e.g.
    
    ![image-20231214115912134](file:///Users/theo/Library/Application%20Support/typora-user-images/image-20231214115912134.png?lastModify=1731126653)
    
    Becomes:
    
    ![image-20231214115930336](file:///Users/theo/Library/Application%20Support/typora-user-images/image-20231214115930336.png?lastModify=1731126653)

**总结范式(1NF、2NF、3NF)**

1. 1NF：列的***原子性***(字段不能再分)。

> 不成立例子：家庭地址能划分为省份、市级…。

2. 2NF：不存在_**部分依赖**_(字段只和联合主键的一部分有关系)。

>不成立例子：学号和课程编号(联合主键)、姓名、课程名称。

>课程名称只和课程编号有关，姓名只和学号有关。

>字段必须和所有主键都有依赖才能构成2NF。

3. 3NF：不存在***传递依赖***(字段和主键不是直接关联)。

>例子：学生和班级表，班级信息和班级编号有直接关系， 但是和学生是间接关系。

>只有字段和所有主键全都是直接依赖才能构成3NF。