1 / 13

D atabases : Fundamentals

C20.0001 Computer-Based Systems for Management Support . D atabases : Fundamentals. Kevin Brabazon. Learning objectives for Session 11. Relational database fundamentals. Get a basic idea about the difference between OLTP and OLAP

ainsley-velez
Download Presentation

D atabases : Fundamentals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C20.0001 Computer-Based Systems for Management Support Databases : Fundamentals Kevin Brabazon

  2. Learning objectives for Session 11 Relational database fundamentals • Get a basic idea about the difference between OLTP and OLAP • Understand what a database is, and how it is different from a DBMS and and a DBA • Understand what relational databases are • Identify and distinguish between the following parts of a relational database: tables, records, fields, field values • Understand three types of anomalies that arise from un-normalized data • Understand what primary keys and foreign keys are • Understand entities and primary keys

  3. OLTP and OLAP OLTP: Online transaction processing OLAP: Online analytical processing • Gathering input information • Processing that information • Updating existing information to reflect the gathering and processing that has taken place • Manipulating data to support decision making Associated with a specific transaction: A repeated, routine event in the day-to-day working of an organization. Example of a transaction: Placing an order at Amazon.com Performed by transaction processing systems (TPS)

  4. Databases What are databases? Database management systems (DBMS) Database administrator (DBA) • Collections of information that are organized according to the logical structure of that information • a software application with which you can create, store, organize and retrieve data from one or many databases • Oracle 8, Access, Sybase, Informix, DB2 • Person responsible for the development and management of an organization’s databases

  5. Relational Databases Relational databases • Databases that use a series of logically related two-dimensional tables to store their information • Tables are comprised of fields/records, which in turn contain field values Field Table Record Field value Student Last Name SS# DOB Major Smith 100201122 06/11/84 IS Kim 200202222 1/1/85 FIN Davis 300201232 12/31/81 MKT Pat 999132212 3/3/88 ACC

  6. Relational Databases Relational Database Tables Records Fields Field values Bytes, bits Field Student Table Last Name SS# DOB Major Smith 100201122 06/11/84 IS Kim 200202222 1/1/85 FIN Davis 300201232 12/31/81 MKT Pat 999132212 3/3/88 ACC Record Field value

  7. Why do we need relational databases? Problems arise when data is stored in one big file

  8. Anomalies in un-normalized data Insertion anomalies Deletion anomalies Update anomalies • Inability to insert a piece of information about an object that can exist independently of another object without having to insert a piece of information about the other object • The loss of a piece of information about one object when a piece of information about a different object is deleted • A need to change the same piece of information about an object multiple times • Example: Adding a new customer/book before it is ordered • Example: Deleting an order => deleting a customer/book • Example: Changing Bill Gates’ address

  9. What’s wrong with this Data?

  10. A normalized version of Amazon’s data Normalization • The process of assuring that a database can be implemented effectively as a set of two-dimensional tables • Prevents insertion, deletion and update anomalies

  11. Connecting tables together Primary keys Foreign keys Integrity constraints • A field (or group of fields in some cases) that uniquely describes each record in a table • Examples: Customer ID, ISBN, Order# • A field that is a primary key in one table and appears in a different table (though not as the primary key) • Examples: Customer ID in Orders Rules that help ensure quality of information

  12. Defining Entity Classes and Primary Keys • Entity class - a concept - typically people, places, or things - about which you wish to store information and that you can identify with a unique key (called the primary key). • Primary key - a field (or group of fields in some cases) that uniquely describes each record. • Instance - an occurrence of an entity class that can be uniquely described.

  13. Defining Entity Classes and Primary Keys • What are the entity classes and primary keys for the report below?

More Related