Overview of the chapter
10.1 Database Characteristics
Advantages and characteristics of database
10.2 Entity-relationship Modeling
10.4 Structured Query Language
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
Solution with database
Repeated record. An error.
Manager accidentally enters a teacher's information twice.Manager accidentally enters a teacher's information twice.
Database will ensure entity integrity. <br/>It will not allow to insert a value for a primary key which already exists.
Same data stored more than once
The teacher's home address is repeated in every table.The teacher's home address is repeated in every table.
Use relational database.
Data doesn't follow same pattern.
A null entry
Database will reject the entry and ask for re-entry.
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.<br />
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.
File structure defined to suit specific programs. <br/>It will not support new applications. <br/>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.
According to ANSI, databases has three levels:
Individual user and programmer uses <br/>(Where you accesses and and enters data using queries / PHP.)
Gives single universal view of the database. <br />
Controlled by database administrator (DBA) <br />DBA: A person who uses the DBMS to customize the database — to suit user and programmer requirements.<br/>(You are a DBA when you access localhost/phpmyadmin, and you construct the database)
Where DBMS (Database management system) lies.<br />DBMS: Software that controls access to data in a database.
Defines the structure of storage of data on the disk.
(Where MySQL, the database software, is written.)
From A-level computer science textbook
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..
Set access rights for users
Ensure an interrupted database transaction cannot leave database in an undefined state.
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:
10.2 Entity-relationship Modeling (ER)
Used in a top-down, stepwise refinement (ch.12) approach to database design.
The diagram shows relationship between databases. Relationships can be:
Read the branches towards your direction.
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.
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:
This is 2NF. Further normalization (3NF) is required.
Normalization has three steps.
Unnormalized form (UNF)
1st normalized form (1NF)
2nd normalized form (2NF)
3rd normalized form (3NF)
Summarize data given to attributes.
(BookingID, VenueName, VenueAddress1 ,VenueAddress2, Date, BandName, NumberOfMembers, Headlining)
Split data into two tables. One with repeating attributes, other with non-repeating attributes.
Two tables have no data redundancy in either.
Booking(BookingID, VenueName, VenueAddressl, VenueAddress2, Date)<br /><br />Band-Booking(BandName, BookinglD(fk), NumberOfMembers, Head lining)
Attributes are dependent on primary key(s).
Execute only on the table with repeating attributes.
Either a) Single primary key, or<br />b) Compound primary key, with all non-key attribute dependent on both components.
Booking(BookinglD, VenueName, VenueAddressl, VenueAddress2, Date) <br /><br />Band-Booking(BandName(fk), BookinglD(fk), Head lining)<br /><br />Band(BandName, NumberOfMembers)<br />------------------------------------- <br/>NumberOfMembers is only dependent on BandName. So it's moved out to a new table.
For each non-key attribute, check if it is dependent on both parts of the compound primary key.<br /><br />If the attribute is only dependent on one part of the compound primary key, move it out to a new table.
Execute on all tables.
Each non-key attribute is dependent on <br />a) The key, b) The whole key, and c) Nothing but the key.
Booking(BookinglD, VenueName, Date)<br /><br />Band-Booking(BandName(fk), BookinglD(fk), Head lining)<br /><br />Band(BandName, NumberOfMembers)<br /><br />Venue(VenueName, VenueAddress1, VenueAddress2)<br /><br />————————————-<br/>VenueAddress is dependent on a non-key attribute, VenueName. So a new table Venue is create————————————-<br/>VenueAddress is dependent on a non-key attribute, VenueName. So a new table Venue is created
For each table, examine if there are any non-key dependencies. (They depend on a non-key attribute)<br /><br />Move them out to a new table.
A ER diagram will describe 2NF but not necessary 3NF.
All operations in SQL, even if provided with user interface, would eventually translate to SQL.
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
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 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 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:
INSERT INTO ... VALUES...
DELETE...FROM ... WHERE...
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>)
DELETE FROM <table_name> WHERE <CONDITION>;
# Condition is a evaluation sentence, such as,
# name = "John"
# Note that single equal signs are used.
SET <field1> = <value1>, <field2> = <value2>
#could update multiple at a time
# 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
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)
Noticed anything wrong with the notes
Want to recommend any new points
Or other complaints or advice
Then Shoot it out! @Astatine-Tian and @JohnLiu will fix it ASAP!
Link back to the notes:
Chapter 1: Information Representation
Chapter 2: Communication and Information Technology
Chapter 3: Hardware
Chapter 5: Processor Fundamentals
Chapter 7: System Software
Chapter 8: Security
Chapter 9: Ethics and Ownership
Chapter 10: Database
We do sincerely appreciate your feedback!