Chapter 10: Database
Overview of the chapter
- 10.1 Database Characteristics [->Page 2]
- Advantages and characteristics of database
- Database Architecture
- Table Structure
- 10.2 Entity-relationship Modeling [->Page 3]
- 10.3 Normalization [->Page 4]
- 10.4 Structured Query Language [->Page 5]
- Data Type
- DDL
- DML
Next page: 10.1 Database Characteristics
10.1 Database Characteristics
Advantages of database
File-based data systems contains many issues as listed. Database could solve these issues easily.
Issue with file-based system | Explanation | Example | Solution with database |
Data Duplication | Repeated record. An error. | Manager accidentally enters a teacher’s information twice. | Database will ensure entity integrity. It will not allow to insert a value for a primary key which already exists. |
Data redundancy | Same data stored more than once | The teacher’s home address is repeated in every table. | Use relational database. |
Data inconsistency | Data doesn’t follow same pattern. | A null entry | Database will reject the entry and ask for re-entry. |
Referencial integrity | Wrong data entered | A teacher name entered wrong | Database will check the name in another table “Teachers”. It rejects the entry if no match is found.![]() |
Data privacy | Everyone could view the whole document | A student wanted to know his teacher’s e-mail address, but get everything instead, including his salary. | Database manages access controls. |
Data dependency | File structure defined to suit specific programs. * It will not support new applications. * Change of file structure requires change of program. | Adding a new property “Teacher nationality” to the file requires changes to the whole program. | Database is flexible. |
Database architecture
According to ANSI, databases has three levels:
Level | Definition |
---|---|
External level | Individual user and programmer uses (Where you accesses and and enters data using queries / PHP.) |
Conceptual level | * Gives single universal view of the database. * Controlled by database administrator (DBA) DBA: A person who uses the DBMS to customize the database — to suit user and programmer requirements. (You are a DBA when you access localhost/phpmyadmin, and you construct the database) |
Internal Level | * Where DBMS (Database management system) lies. * Defines the structure of storage of data on the disk. DBMS: Software that controls access to data in a database. (Where MySQL, the database software, is written.) |
Physical Storage | Hardware storage. |

Functions of Database Management System (DBMS) include:
- Data dictionary is hidden from everyone except the Database Administrator (DBA).
- Improve searching speed by using index
- A secondary table associated with an attribute that has unique values.
- It contains attribute values and pointers to corresponding tuple in the original table.
- Index can be on primary or secondary key..
- Security
- Set access rights for users
- Backup
- Ensure an interrupted database transaction cannot leave database in an undefined state.
Table structure
In relational databases, data is stored in relation. (which is obvious)
The relational database consists of multiple relational tables. Here is an example of a relational table:

Next page: 10.2 Entity-relationship Modeling
10.2 Entity-relationship Modeling (ER)
Used in a top-down, stepwise refinement (ch.12) approach to database design.
Relation view:
The diagram shows relationship between databases. Relationships can be:
- One-to-one
- One-to-many
- Many-to-many

e.g. in the 1:M case, read “one A corresponds to many B.”
To add more detail, a second branch could be added to show the range of values possible.

Normalization:
Relational databases do not allow M:M situations. Foreign keys cannot be used since there’s no field available for a primary key field.
A link entity will be inserted to change M:M to a 1:M:1 situation.


The link entity would have a compound primary field, as shown in textbook example:

Next page: 10.3 Normalization
10.3 Normalization
Normalization has three steps.
Unnormalized form (UNF)
⬇️
1st normalized form (1NF)
⬇️
2nd normalized form (2NF)
⬇️
3rd normalized form (3NF)
Form | Operation required | Characteristics | Textbook example |
---|---|---|---|
UNF | Summarize data given to attributes. | (BookinglD, VenueName, VenueAddress1 ,VenueAddress2, Date, BandName, NumberOfMembers, Headlining) | |
1NF | * Split data into two tables. One with repeating attributes, other with non-repeating attributes. | * Two tables have no data redundancy in either. * Attributes are dependent on primary key(s). | Booking(BookinglD, VenueName, VenueAddressl, VenueAddress2, Date) Band-Booking(BandName, BookinglD(fk), NumberOfMembers, Head lining) ————————————- From the graph it is easy to find the repeated groups. |
2NF | Execute only on the table with repeating attributes. * For each non-key attribute, check if it is dependent on both parts of the compound primary key. * If the attribute is only dependent on one part of the compound primary key, move it out to a new table. | 2NF table either has either: a) Single primary key, or b) Compound primary key, with all non-key attribute dependent on both components. (No partial dependency) | Booking(BookinglD, VenueName, VenueAddressl, VenueAddress2, Date) Band-Booking(BandName(fk), BookinglD(fk), Head lining) Band(BandName, NumberOfMembers) ————————————- NumberOfMembers is only dependent on BandName. So it’s moved out to a new table. |
3NF | Execute on all tables. * For each table, examine if there are any non-key dependencies. (They depend on a non-key attribute) Move them out to a new table. | Each non-key attribute is dependent on the primary key only. i.e. Dependent on: a) The key, b) The whole key, and c) Nothing but the key. A ER diagram will describe 2NF but not necessary 3NF. | Booking(BookinglD, VenueName, Date) Band-Booking(BandName(fk), BookinglD(fk), Head lining) Band(BandName, NumberOfMembers) Venue(VenueName, VenueAddress1, VenueAddress2) ————————————- VenueAddress is dependent on a non-key attribute, VenueName. So a new table Venue is created. |
Next page: 10.4 SQL
10.4 SQL
All operations in SQL, even if provided with user interface, would eventually translate to SQL.
SQL has a different set of datatypes. Refer to the following table:
Data type | MySQL | Python |
---|---|---|
String | VARCHAR TEXT | str |
Integer | INT | int |
Float | FLOAT DECIMAL DOUBLE | float |
Time | TIME | |
Date | DATE |
SQL is divided into two sections: Data definition language and Data manipulation language.
10.4.1 Data definition Language (DDL)
DDL changes the structure of a table, for example, adding a new row or changing its data type.
Commands required to master:
- Create a new table
- Alter the table.
a) Create a table
# You will be asked to create a table, and add primary key and foreign key.
CREATE TABLE <table_name> (
<attribute_name_1> <data_type> PRIMARY KEY, # Primary key
<attribute_name_2> <data_type>,
<attribute_name_3> <data_type>,
...
FOREIGN KEY (<attribute_name_2>) REFERENCES <table_name>(<attribute_name>) #Add foreign key
);
# Note the brackets!
b) Alter fields
Operations are based on command “ALTER TABLE…”.
Keywords quick reference:
- Add field / properties
- ADD
- Change properties
- MODIFY
- CHANGE
Add field / properties
#Add foreign key and primary key
ALTER TABLE <table_name> ADD PRIMARY KEY (<field_name>);
ALTER TABLE <table_name> ADD FOREIGN KEY (<field_name>) REFERENCES <table_name>(<field_name>);
# Remember the brackets!!!
# Add a new field
ALTER TABLE <table_name> ADD <field_name> <data_type>
# Delete the field
ALTER TABLE <table_name> DROP <field_name>
Change properties
# Change the datatype of the field
ALTER TABLE <table_name>
MODIFY <field_name> <data_type>;
# Rename / change data type of a field
ALTER TABLE <table_name>
CHANGE <old_field_name> <new_field_name> <datatype>
10.4.2 Data manipulating language (DLA)
DLA change the values, not the table structure.
Key words quick reference:
- Add value:
- INSERT INTO … VALUES…
- Delete value:
- DELETE…FROM … WHERE…
- Change value:
- UPDATE…SET…WHERE…
- Query
- SELECT…FROM…WHERE…
- INNER JOIN
Insert values
INSERT INTO <table_name> (<field1>, <field2>)
VALUES (<value1, value2>);
# If the order of attributes are known, there's a easier way:
INSERT INTO <table_name>
VALUES (<value1>, <value2>)
Deleting values
DELETE FROM <table_name> WHERE <CONDITION>;
# Condition is a evaluation sentence, such as,
# name = "John"
# Note that single equal signs are used.
Change values
UPDATE <table_name>
SET <field1> = <value1>, <field2> = <value2>
#could update multiple at a time
WHERE <condition>;
Query
# Standard Query
SELECT <field_name> FROM <table_name> WHERE <CONDITION>;
# Query across multiple tables
SELECT <field1>, <field2>
FROM <table1>, <table2>
WHERE <table1>.<field1> = <table2>.<field1>
#⬆️Linking condition!! Very Important!!! Links a primary key in table 1 to its foreign key in table 2!!!
AND <OTHER_CONDITIONS>; #Standard query conditions
Joining multiple tables
When two tables are linked through foreign key, they can join together based on the foreign key, to form one single temporary table. There are multiple methods to join two tables, but you only require to know the method INNOR JOIN.

For example, if you want to join table 2 to table 1:
SELECT <table1>.<field1>, <table2>.<field1> #can multiple fields
FROM <table1>
INNER JOIN <table2> #...Join table2 to table1
ON <table1>.<field1> = <table2>.<field2>;
# ⬆️Links PRIMARY KEY in table 1 to FORIGN KEY in table 2!!!
WHERE <condition> #Other Standard query conditions
Note that full name of the field is required. (Must declare which table)
Example question of joining: (A1 Database test)
Joining tables by traditional method: (From Teressa, @astatine-213)

Use Inner Join: (From me)
