INFS1000 Lecture Notes - Lecture 4: Mysql, Cardinality, Data Type
4 Jun 2018
School
Department
Course
Professor
WEEK 4: DATABASES AND INFORMATION
WHAT IS THE PURPOSE OF A DATABASE?
• To store and organise information on important business objects
o Things, people and processes with more than a single instance that we want to collect information about
o E.g. customers, products, suppliers, transactions, equipment, locations, staff
• Spreadsheets only keep list of a single business objects
• Databases keep lists of multiple business objects + interrelationships
o E.g. customers + purchases, suppliers + deliveries, equipment + availability etc.
• E.g. Majestic River Ventures (MRV)
o Runs river rafting tours on rivers in Victoria + NSW
o Takes bookings from guests worldwide
o Has to know availability of free spaces at any given time to allow new bookings
o Hires independent guides
o Needs to keep track of equipment
o Guides/equipment have to be allocated
WHAT DOES A DATABASE CONTAIN?
• Database: a self-describing collection of integrated records
1. Tables
2. Relationships among tables
3. Metadata
TABLES
• Represent business objects e.g. student, customer, product etc.
• Hierarchy of data elements:
o Bytes/Characters: grouped into columns/fields
o Columns/Fields: grouped into rows/records
o Rows/Records: grouped into tables/files
o Tables/Files
RELATIONSHIPS
• Relationships among tables:
o Exist b/w rows in different tables
§ Implement and represent business rules
o Students’ interactions with University by email or during office visits
§ 3 tables: Student, Email, Office_Visit
§ 2 relationships: Students send emails; Students visit office
• Relationships between rows
o Linked by keys = values in one table relate to rows/records in other tables
o Primary Key: column that uniquely identifies a row
o Foreign Keys: primary keys in other tables
§ 1:M à PK in 1 side is imported into M side as FK
Document Summary
To store and organise information on important business objects: things, people and processes with more than a single instance that we want to collect information about, e. g. customers, products, suppliers, transactions, equipment, locations, staff. Spreadsheets only keep list of a single business objects: databases keep lists of multiple business objects + interrelationships, e. g. customers + purchases, suppliers + deliveries, equipment + availability etc. What does a database contain: database: a self-describing collection of integrated records, tables, relationships among tables, metadata. Tables: represent business objects e. g. student, customer, product etc, hierarchy of data elements, bytes/characters: grouped into columns/fields, columns/fields: grouped into rows/records, rows/records: grouped into tables/files, tables/files. Relationships: relationships among tables, exist b/w rows in different tables. Implement and represent business rules: students" interactions with university by email or during office visits. 2 relationships: students send emails; students visit office: relationships between rows.
