Understanding Structured Data and Relational Database Management Systems (RDBMS), Slides of Database Management Systems (DBMS)

Download Understanding Structured Data and Relational Database Management Systems (RDBMS) and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Topic 01

Overview, Relational Model,

RDBMS, Database, MS Access, Table, Record, Field

Primary Key, Filter & Query

Unit Overview

Aims | Content | Assessment | Schedule | Tools

Unit Schedule

Topic Content Note 1 Fundamental DBMS Concepts and Tools 2 Relationships, Indexes, and Queries 3 Queries and Data Import/Export 4 Business Intelligence Tools 5 SQL and Modelling Part 1 6 SQL and Modelling Part 2 7 SQL and Modelling Part 3 8 SQL and Modelling Part 4 9 Static Data, Variable Data, More Modelling, and Views 10 Normalisation, NoSQL, and JSON 11 Transactions, ACID, ETL, and Data Warehousing 12 Summary and Review

Unit Tools and Techniques

Microsoft Access

Microsoft Power BI

SQL and iSQLJr (Oracle)

ERD Modelling (and Microsoft Visio)

Microsoft Azure DocumentDB

Understanding Data

Challenges | Unstructured | Semi-Structured | Structured

The Modern Data Challenge

Organisations need to store and retrieve usually large amounts of data

Data can be divided into three major categories

• Structured Data
• Semi-Structured Data
• Unstructured Data

Structured Data is typically used by Relational Database Management Systems (RDBMSs) such as Access, Oracle, SQL Server, MySQL.

Semi-Structured Data

Semi-structured data is information that doesn't match the

requirements of a relational database.

The data is organized / arranged that makes it easier to analyze.

Examples of semi-structured data include XML documents and

NoSQL databases.

We will briefly deal with the topic of semi-structured data in future

weeks

Structured Data

Relational Database Management Systems require data to be stored in a

very structured way.

These systems deal with data that has a repetitive pattern or format.

Consider Student data stored in a University. While every student is

different, the university want to store data in the same format for every

student. Data Types are also specified for each piece of information

• Student ID – Numeric HomeAddress - Alpha
• Student Name – Alpha PhoneNo - Numeric
• Gender – Alpha NextOfKin - Alpha
• DateOfBirth - Date

Relational Data Model

RDBMSs are based on the Relational Data Model

• Developed by Ted Codd in 1970.
• Data is represented in the form of two-dimensional tables.

Each two-dimensional table has the following properties:

• A set of uniquely named Columns / Attributes
• A list of unnamed/unnumbered Rows
• The order of the rows is irrelevant.

A Row consists of a sequence of Attributes

• One cell for each Attribute
• Only one value per cell is allowed.

All Relational Database Management Systems are based on the Relational Data Model.

Relational Data Model (cont.)

Table

Uniquely named columns called Attributes Attribute

ID EmpName Branch Salary

Row 1001 Fred Blogs Haw 89000

1004 Emma Jevs City 125000

1006 Dave Rigg Haw 65000

RDBMS

A RDBMS is a collection of programs that allow developers /

users to store & retrieve data from relational databases

It allows users to perform CRUD (create, read, update &

delete) operations on data in the tables. E.g:

• Create a student
• Retrieve a student's details
• Update a student's details
• Delete a student details from a table

Setting up a RDBMS

Tables

• Follow a 2 dimensional structure
• Each row of data is a Primary Key value
• No duplicates e.g. Student ID

Constraints can be added to validate data

• Student ID is correct length
• Student type is PG or UG (post or undergraduate)
• Student is enrolled in a degree that actually exists

RDBMS and Database Servers

A database server is a computer that is networked to other computers

The database server stores databases

Users on the network can access the data stored in the databases

There is only one copy of the data (excluding backups etc.)

RDBMSs allow multiple users on the network to update data in database tables.

• Many people can check the price of product 20
• Many people may chose to enroll in INF10002 simultaneously
• Many users may purchase tickets for a flight at the same time.

RDBMS and Databases Servers (cont.)

Imagine trying to use a Word Processor or Spreadsheet to do this !!!

Could Telstra store details about all of their customers in a Word Processing document or spreadsheet? Loading! Searching! Updating!

Could ANZ store all deposits and withdrawals of all customers in a single spreadsheet? Thousands / Millions / Billions of rows.

Imagine trying to retrieve all the deposits made by Cust 123 4 over the past 24 months from spreadsheet data

• Size of sheets
• Load time
• Lack of computer memory
• Unnecessary loading of other customers banking transactions.