Managing Data Resources Flashcards
Database
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”
The black box database model
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)
Storing data
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
Advantages of computerised database systems
Easy to backup Less space than a filing cabinet Easy to search Easy to share (copy, network) Easy to transport
Examples of non computer databases
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)
Examples of computerised databases
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
File management systems
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
DBMS
DataBase Management System (DBMS) enables a pool of related data shared by multiple application programs
RDMS
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
The major issues addressed by RDMS
Redundant data
Data integrity
Ideally each data item is stored once in the entire system
Redundant data
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
Data integrity
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
Important ideas in database design
Identifying Tables (Entities) Identifying Fields (Attributes)
plus
Relationships (Links)
Entity-Relationship Diagrams (ERDs)
Entities (Tables)
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.
Examples of common entities:
Department Venue Customer Order Item Branch
Fields (Attributes,Columns)
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
RELATIONSHIPS (LINKS)
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
ERDS
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
RELATIONSHIPS BETWEEN TABLES
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):
WHAT IS AN ERD
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)
What are ERDS used for
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.
MANDATORY/OPTIONAL
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
Blackbox model diagram
photo 1
3 TYPES OF ERDS
PHOTO 2
ONE TO MANY EXAMPLE
PHOTO 3
RELATIONSHIPS BETWEEN TABLES PHOTO
PHOTO 4
ERD DRAWING
PHOTO 5
MANDATORY/OPTIONAL ERD
PHOTO 6
ERD PROBLEM
PHOTOS 7-9
