Chapter 10: Database

Nov 16, 2019

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 systemExplanationExampleSolution with database
Data DuplicationRepeated 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 redundancySame data stored more than onceThe teacher’s home address is repeated in every table.Use relational database.
Data inconsistencyData doesn’t follow same pattern.A null entryDatabase will reject the entry and ask for re-entry.
Referencial integrityWrong data enteredA teacher name entered wrongDatabase will check the name in another table “Teachers”. It rejects the entry if no match is found.
Data privacyEveryone could view the whole documentA student wanted to know his teacher’s e-mail address, but get everything instead, including his salary.Database manages access controls.
Data dependencyFile 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:

LevelDefinition
External levelIndividual 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 StorageHardware storage.
From computer science course book
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..
  • 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
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.

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.

Textbook example

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

This is 2NF. Further normalization (3NF) is required.

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)
FormOperation requiredCharacteristicsTextbook example
UNFSummarize 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.
2NFExecute 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.
3NFExecute 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 typeMySQLPython
StringVARCHAR
TEXT
str
IntegerINTint
FloatFLOAT
DECIMAL
DOUBLE
float
TimeTIME
DateDATE

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)

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.