Chapter 10: Database

Overview of the chapter

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

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:

1. Create a new table
2. 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)