Please enable JavaScript.
Coggle requires JavaScript to display documents.
Relational Databases and Introduction to Data Warehousing (Quality of ER…
Relational Databases and Introduction to Data Warehousing
Data Modelling and Relational Databases
Relational Database
Relationships
Entities
Data Modelling
stored in a database
determining
What data
What relationships
conceptually design our data needs
Entity Relationship Diagrams
communication tool between business
people and IT people
Picture of the people, places, objects, things, events, or concepts, their characteristics and relationships
Entity Relationship Diagrams
Entities
Person
place
object
event
concept
Attributes
• should not change over time (age)
• must have unique, non-null values
• use as few attributes as possible
Typically underlined
Relationships: Association between entities
ER Process
Identify relationships
Identify constraints 关系基数
Identify entity types
Iterate 迭代
Identifying the Attributes
A Foreign Key
A Composite Key
A Primary Key
No duplicates
blanks allowed
Database Management Systems enforce two integrity rules
Entity Integrity- Makes entities traceable 使实体可追溯 每个表都有唯一值的列，主键不允许重复或空白
Referential Integrity- Ensures validity of relationships 确保关系的有效性 学生不能注册课程，除非他们已经有学生关系的记录。
Purpose
basis for database and IT system design
IT people and business people
To understand and describe data requirements
Quality of ER Models
Correct: Conforms to ER syntax rules
Complete: Contains all required information
Simple: Contains minimum number of possible entities and relationships
Understandable: Concepts in model are easy to understand
Flexible: Ease with which model copes with change
Integrity: Contains all required “business rules”
Integration: Consistency with other organisational data
Implementability: Ease with which model can be implemented
Normalization
Unnormalised Data
Deletion Anomaly: If student 425 withdraws, we lose all record of course C400 and its fee!
Update Anomaly: If the fee for course C200 changes, we have to change it in multiple records (rows), else the data will be inconsistent.
Insertion插入 Anomaly异常: A new course cannot be added until at least one student has enrolled (which comes first student or course?)
Querying a Database: Structured Query Language (SQL)
Select
group by: attributes (custtype) indicate categorisation of results结果的分类 ➕平均 汇总数据
order by: attributes(custlastname)Sort the result based on the criteria 根据条件对结果进行排序
where: attributes=
limit: 5
from: table
select: Attributes
join
Natural Join: Join the tables with foreign keys where the primary key and foreign key have the same name
Inner Join the tables with foreign keys!
The join condition tells the database how it should match the
records from one table to the other.
analyse and interpret ER models including
entities, attributes, different types of keys and relationships
explain SQL SELECT, Aggregate functions and JOIN Statements