Download
1 / 15
150 likes | 243 Views
Database & DBMS. The data that goes into transaction processing systems (TPS), also goes to a database to be stored and processed later by decision support systems (DSS) or other information systems (see next 2 slides). Information (processed data) can also be stored in a database.
E N D
Database & DBMS • The data that goes into transaction processing systems (TPS), also goes to a database to be stored and processed later by decision support systems (DSS) or other information systems (see next 2 slides). • Information (processed data) can also be stored in a database. • The size of database is huge as some organizations store data up to 20 years, e.g. government, banks. • Crucial to have the huge database managed properly; database management systems (DBMS) can help e.g. Access, Oracle, DB2, FoxPro, Sybase.
Functional Management Information Systems Draw Data from the Organization’s TPS
Database Terminologies • A database - a collection of tables. Tables are also called relations. • 2-dimensional table: a series of row/column intersections • A table represents one type of entity e.g. a table for agents, and a separate table for customers. • A table contains related entities of a particular type, e.g. a table of different customers’ records, a table of different agents’ records.
Relational Database Model Figure 1.11
Relational Database • Attribute (a column or a field) of a table • An attribute is one characteristic (or property) of that type of entity e.g. customer’s address, phone number, sex, birth date etc. • Record (a row) of a table • Represents a particular entity e.g. a particular customer or a particular agent. • Has logically connected fields that describe a particular entity e.g. a person, place, a thing, an object, a concept or thing about which data are collected.
Relational database • Each entity must have a unique key or identifier. • Tables must have an attribute(s) that uniquely identify each row (or entity) – primary key. • Values of a column all have the same data format. • The order of the rows and columns is immaterial (unimportant) to the DBMS e.g. Microsoft Access. • Primary key of each table must: • Be Unique • Have No ‘null’ value in key • consist of a single field or multiple fields. • E.g. HKID, car registration number.
Relationship between ‘Agent’ and ‘customer’ • ‘Agent’ and ‘customer’ are 2 separate entity types (in 2 different tables). • ‘Agent’ and ‘customer’ have a 1-to-many relationship because of the following: - Each ‘agent’ is in charge of 1 or more ‘customers’, but each ‘customer’ only deals with 1 particular ‘agent’.
Relational Database Schema • See next slide – a relational schema produced by Microsoft access. • The “AGENT_CODE” is the unique identifier for each agent. • The “CUS_CODE” is the unique identifier for each customer. • Here, “CUSTOMER” represent the “many” side, because an agent can have many customers. • The “AGENT” represents the “1” side because each customer has only a single agent.
Simple Relational Database Figure 2.2
Keys • Primary key • to uniquely identify an entity from many entities of the same type. • Foreign key • Values must match primary key in another table • Used to have relationships (1:1, 1:M or M:N) with another table.
Example 1:M Relationship Figure 2.20
Database Design and schema • IT professionals and programmers are responsible for the design (schema) of the database: table structures, relationships between tables, primary and foreign keys etc. • Business managers or users of a database should understand the design of a database and query this database for information or data. • Use Access’s QBE to query a simple database.
