Session 2: Relational Databases

Ch 4

Advantages of Databases : why it’s pref over traditional file

• data is integrated into large pools of data

  • minimize data redundancies and inconsistencies 

• Data is shared between diff units

  • cross functional analysis becomes efficient 

• Data is independent of the programs that use the data

  • each can be changed without changing the others 

Database Management Systems (DBMS)

• software system that manages and controls the data and interfaces between the data and the applications

Types of Databases

• relational

• hierarchical 

• Network

• Object Oriented 

Disadvantages of storing all data in one flat table

• data redundancy 

• Anomalies may arise

  • Update anomaly

  • Insert Anomaly

  • Delete Anomaly

Update Anomaly

• changing a customer’s address involves searching the entire table and changing every occurrence of that customer’s address

insert Anomaly

• there is no good way to store information about prospective customers or new products until a sales transaction is complete

Delete Anomaly

• deleting a sales record could delete information about that customer or that product, resulting in unintended consequences

Solution of Anomalies

• relational database 

  • Create multiple two dimensional data tables that are connected to form a relational database

    • independent, but still related

Elements of Data Hierarchy in relational databases

• Database is a set of interrelated, centrally coordinated data files 

• File (table) is a related group of records

• Record (row) is a related group of fields 

• Field (column) Specific attribute for the record 

Primary key in relational databases

• an attribute or combo of attributes that uniquely identifies a specific record/row in a table

Foreign Key in relational databases

• an attribute in a table that is also a primary key in another table and is used to link the two tables

Fact Table (ex. Sale)

• contains facts abt a business process (sales of cars)

• Contains foreign keys to link it to dimension tables

• often long and contains more numerical fields

• located at the center of star schema

Dimension Table (Salesperson)

• contains attributes of the business process (ex. who made the sales) and those attributes can be used as filters or grouping variables in data analysis

• contains primary key to link to fact tables

• often short and contains more descriptive fields

• surrounding fact table in a star schema

Cardinality (or relationship between tables)

• one to many or many to 1 (1:* or *:1)

  • the column in one table (ex. fact table) can have many instances of a value, and the other related table

• one to one (1:1)

  • the column in both tables has only one instance of a value, probably because that variable is a primary key of both tables

• many-to-many (*:*)

  • this type of relationship could create errors