8.1 Database Concepts

File Based System

Data is kept in separate files on a computer, where users can access, modify, or delete it as needed.

Disadvantages:

• Data Redundancy: Duplicate data across files wastes space.
• Lack of Security and limited control over data access.
• Challenging for multiple users to access or update files simultaneously.
• Harder to sort or filter data
• There is no enforced structure for organizing file
• Data can exist in various formats, making it hard to use

Database Terminologies

• Database: Organized collection of non-redundant interrelated data stored electronically
• DBMS (Database Management System): Software that facilitates the creation, maintenance, and manipulation of databases
• Entity: A distinct object or concept that can be identified and stored in a database, often representing a real-world object
• Table: A structured arrangement of data in rows and columns within a database, representing entities
• Record: A single entry in a table, consisting of fields that contain related data about an entity
• Field/Column: Contains specific property about a record
• Tuple: A single row/record
• Attribute: A field/ column
• Primary key: Attribute/s which is the unique identifier
• Candidate key: Attribute/s that has potential to be a primary key
• Secondary key: Candidate keys not chosen as the primary key
• Foreign key: Attribute/s relating 2 different tables
• Referential integrity: A constraint ensuring that relationships between tables remain consistent, preventing orphaned records
• Indexing: Creating a secondary key on an attribute enables quick access when searching by that attribute; however, the index must be updated whenever the table data is modified.

Example Database
Let’s Say there is a database with two tables, where one is for customers and one is for vehicles.

Customer Table

Vehicle Table

Examples from the above database:

• Tables: Vehicle, Customer
• Entity: Vehicle, Customer
• Record: Row 2 to 8 in Vehicle table
• Field/Column/Attribute: Make, Model, Year in Vehicle Table
• Tuple: Row 2
• Primary Key: License_No in Customer table and VIN in Vehicle table
• Candidate Key: Phone, License_No in Customer Table
• Secondary Key: Phone in Customer table
• Foreign Key: Buyer in Vehicle table

Entity Relation Diagram Symbols

Normalization:

Not Normalized Example:

1st Normal Form (1NF): Contains no repeating attribute or groups of attributes. The intersection of each tuple and attribute contains only 1 value.
Example: (Split Cars and Models into individual rows)

2nd Normal Form (2NF): it is in 1NF and every non-primary key attribute is fully dependent on the primary; all the incomplete dependencies have been removed.

Example: Model depends on car, The others depend on License_No

Customer Table

3rd Normal Form (3NF): It is in 1NF and 2NF, and all non-key attributes are fully dependent on the primary key. There should be no inter-dependencies between non-key attributes.

Example: The Car Table and Customer_Car Table stay the same. The Customer Table is broken down because Area depends on City.

Note:
MANY-TO-MANY functions cannot be directly normalized to 3NF, must use a 2 step process e.g. The 2nd diagram below represents 3rd normal form