10 Database



  • Overview of the chapter

    • 10.1 Database Characteristics

      • Advantages and characteristics of database

      • Database Architecture

      • Table Structure

    • 10.2 Entity-relationship Modeling

    • 10.3 Normalization

    • 10.4 Structured Query Language

      • DDL
      • DML

    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.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.
    Data redundancy 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 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.<br />
    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. <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.

    Database architecture

    According to ANSI, databases has three levels:

    Level Definition
    External level Individual user and programmer uses <br/>(Where you accesses and and enters data using queries / PHP.)
    Conceptual level 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)
    Internal Level 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.)
    Physical Storage Hardware storage.

    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:


    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.


    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. (BookingID, 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. Booking(BookingID, VenueName, VenueAddressl, VenueAddress2, Date)<br /><br />Band-Booking(BandName, BookinglD(fk), NumberOfMembers, Head lining)
    Attributes are dependent on primary key(s).
    2NF 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.
    3NF 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.

    10.4 SQL

    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:

    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)


Log in to reply