Managing Data Resources

Question 1

Database

Answer 1

A Database is a system that takes Data as input, and generates Information as output
“a repository for an organised collection of interrelated data files, records and fields”
“a collection of related data organised to assist end-users”

Question 2

The black box database model

Answer 2

Processing in databases is usually hidden from business users
They don’t worry about how it’s done
They do worry about what goes in (data) and what comes out (information)

Question 3

Storing data

Answer 3

Organisations used to store data as paper records, in filing cabinets, and do the processing by hand. There are many advantages, however, if this data is stored electronically in a database on a computer

Question 4

Advantages of computerised database systems

Answer 4

Easy to backup
Less space than a filing cabinet
Easy to search
Easy to share (copy, network)
Easy to transport

Question 5

Examples of non computer databases

Answer 5

List on the wall of a coffee shop
Data is recorded in a structured form
Each order (record) has same structure
Item ordered
Variations to standard order (no salt)
Number of items ordered

Your Address Book
The Telephone Directory (printed)

Question 6

Examples of computerised databases

Answer 6

Keeping track of the files on your PC – The Filing Cabinet model
The computer records information about “directories” on a hard-disk drive

Modern computer-based databases
The RMIT University Student Database
Business transaction database (such as for Hammer Wines)
Database for scheduled plane flights at an airport

Question 7

File management systems

Answer 7

Historically computers stored data in individual files (known as File Management Systems)
To retrieve and manipulate data, software had to be specifically created or customised

Question 8

DBMS

Answer 8

DataBase Management System (DBMS) enables a pool of related data shared by multiple application programs

Question 9

RDMS

Answer 9

Relational Database Management System (RDMS):
Access, Oracle, MySQL, Postgresql, Many others …
A collection of tables (data files) related/joined to one another by common data elements

Question 10

The major issues addressed by RDMS

Answer 10

Redundant data

Data integrity

Ideally each data item is stored once in the entire system

Question 11

Redundant data

Answer 11

Data was duplicated (i.e. Many pieces of data were stored multiple times in many different files, in order to allow business questions to be answered)
Redundancy wastes space and time
Redundancy can lead to inconsistency

Question 12

Data integrity

Answer 12

Inaccurate data. Changes to the data therefore had to be re-entered at many places in the files. If any required changes to the data were missed then the integrity of the database as a whole was compromised
Data should have integrity - It must be
Accurate
Reliable

Question 13

Important ideas in database design

Answer 13

Identifying Tables (Entities)
 Identifying Fields (Attributes)

plus
Relationships (Links)
Entity-Relationship Diagrams (ERDs)

Question 14

Entities (Tables)

Answer 14

Something that the business wishes to store data about
Primary Key
Exists as a set of records
Can be a person, place, object, event, etc.

Question 15

Examples of common entities:

Answer 15

Department
 Venue
 Customer
 Order			
 Item				
 Branch

Question 16

Fields (Attributes,Columns)

Answer 16

Characteristics/Descriptors/Properties of an Entity

Product
Product Code, Description, Supplier ID, Quantity on Hand, Unit Price, Reorder Level

Customer
D, Name, Address, Contact Name, Phone Number, Mobile, Email Address

Sales Order
Sale ID, Customer ID, Date, isShipped

Question 17

RELATIONSHIPS (LINKS)

Answer 17

The associations or relationships (links) between Entities depend on the business rules !!
Create links between entities where required
Can follow links between many entities
There are three types of relationships (links) in this course:
One to one
One to many
Many to many

Question 18

ERDS

Answer 18

One to One
A bank branch has only one manager
A manager manages only one bank branch

One to Many (most common)
A department can have many employees
An employee works in one department

Many to Many
An order can have many items
An item can be part of many orders

Question 19

RELATIONSHIPS BETWEEN TABLES

Answer 19

Tables are joined by relationships, e.g between Employee and Department tables.
On the one side the identifier is unique (There is only one ID for each line in the Department table).
We can also draw relationships between entities in an Entity Relationship Diagram (ERD):

Question 20

WHAT IS AN ERD

Answer 20

Entity-relationship-diagram

ERDs provide a simple diagrammatic way of representing entities (tables) and the relationships (links) between them
A relationship is a statement of a business rule
A relationship has cardinality (i.e. the number of entity instances of Entity B that can (or must) be associated with Entity A)

Question 21

What are ERDS used for

Answer 21

ERDs are a good way to design operational databases.
The designer can try out a number of designs.
Designer/developers can discuss the designs.
The designs guide development at every stage.
They can be read by nondevelopers.

Question 22

MANDATORY/OPTIONAL

Answer 22

Must a record in one table have a matching record in another table?
We need to determine this in order to more accurately model business rules
Note: We can enforce Mandatory/Optional rules in the RDMS

Question 23

Blackbox model diagram

Answer 23

photo 1

Question 24

3 TYPES OF ERDS

Answer 24

PHOTO 2

Question 25

ONE TO MANY EXAMPLE

Answer 25

PHOTO 3

Question 26

RELATIONSHIPS BETWEEN TABLES PHOTO

Answer 26

PHOTO 4

Question 27

ERD DRAWING

Answer 27

PHOTO 5

Question 28

MANDATORY/OPTIONAL ERD

Answer 28

PHOTO 6

Question 29

ERD PROBLEM

Answer 29

PHOTOS 7-9