1 / 27

RELATIONAL DATABASES

RELATIONAL DATABASES. Chapter 3. Quick Review. File-Oriented Systems. Master File 1 Fact A Fact B Fact C. Enrollment Program. Master File 2 Fact A Fact D Fact F. Fin. Aid Program. Master File 1 Fact A Fact B Fact F. Grades Program. File-Oriented Systems. Result in:

forrest-marshall
Download Presentation

RELATIONAL DATABASES

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. RELATIONAL DATABASES Chapter 3

  2. Quick Review

  3. File-Oriented Systems Master File 1 Fact A Fact B Fact C Enrollment Program Master File 2 Fact A Fact D Fact F Fin. Aid Program Master File 1 Fact A Fact B Fact F Grades Program

  4. File-Oriented Systems Result in: • Redundancy • Inconsistency

  5. Database Systems Database Fact A Fact B Fact C Fact D Fact E Fact F Application programs Database Management System Enrollment Program Fin. Aid Program Grades Program

  6. Database Systems • Benefits • Data integration • Data sharing • Reporting flexibility • Minimal data redundancy and inconsistencies • Data independence • Central management of data • Cross-functional analysis

  7. Database Systems • Two separate views of the data • Logical view -- How the user or programmer conceptually organizes and understands the data. • Physical view -- How and where the data are physically arranged and stored.

  8. Logical View—User B Logical View—User A Enrollment by Class DBMS Operating System Database

  9. Subschema--User A Subschema--User B Subschema--User C Smith . . . A Jones . . . B Arnold . . .D Mapping external-level views to conceptual-level schema Classes Enroll Student Cash Receipt Mapping conceptual-level items to internal-level descriptions Student Record Student No. --character [9] Student Name --character [26] SAT Score --integer [2], non-null, index=itemx Class Record Class Name --character [9] Dept No. --integer [4], non-null, index=itemx Course No. --integer [4], non-null, index=itemx

  10. Database Systems • Schemas • A schema describes the logical structure of a database. • There are three levels of schema. • Conceptual level • External level • Internal level

  11. Database Systems • Data Dictionary • Contains information about the structure of the database. • For each data element, there is a corresponding record in the data dictionary describing that element. • Inputs include: • Records of new or deleted data elements. • Changes in names, descriptions, or uses of existing elements. • Outputs include: • Reports that are useful to programmers, database designers, and IS users in: • Designing and implementing the system. • Documenting the system. • Creating an audit trail.

  12. Database Systems • Data definition language (DDL) • Build the data dictionary • Initialize or create the database • Describe the logical views • Specify limitations or constraints on security

  13. Database Systems • Data manipulation language (DML) • Updating data • Inserting data • Deleting segments

  14. Database Systems • Data query language (DQL) • Retrieving records • Sorting records • Ordering records • Presenting subsets

  15. Relational Databases • A data model is an abstract representation of the contents of a database. • The relational data model represents everything in the database as being stored in the forms of tables (aka, relations).

  16. Non-Normalized Relational Tables • Update Anomaly: Problem that arises when attributes that aren’t characteristics of the primary key are stored in that table, then the data item is stored in many different rows. • Insert Anomaly: Problem that arises when attributes that aren’t characteristics of the primary key of a relation are stored in the table. • Delete Anomaly: Problem that arises when attributes that aren’t characteristics of the primary key are stored in a table, so that deleting a row from the table may result in the loss of all information about those attributes that are not characteristics of the primary key

  17. Relational Databases • Basic Requirements of a Relational Database • Every column in a row must be single valued. • The primary key cannot be null. • A foreign key must either be null or correspond to the value of a primary key in another table. • All non-key attributes in a table should describe a characteristic of the object identified by the primary key.

  18. Relational Databases • There are two basic ways to design well-structured relational databases. • Normalization • Semantic data modeling

  19. Relational Databases • Normalization • Starts with the assumption that everything is initially stored in one large table & follows set of rules to decompose the table • Objective is to produce a set of tables in third-normal form (3NF) • Semantic data modeling • Database designer uses knowledge of business processes and information needs to draw a graphical picture of what should be included in the database. • The resulting graphic is used to create a set of relational tables that are in 3NF

  20. Database Systems and the Future of Accounting • May lead to abandonment of double-entry accounting • May alter the nature of external reporting • Accounting information will enhance decision making

  21. Summary We have: • Compared and contrasted file-oriented systems with databases legacy systems • Determined the benefits of database systems • Defined the logical and physical views of a database • Defined a DBMS, schema, and the data dictionary • Differentiated between the three levels of schemas • Discussed the DBMS languages • Defined a relational database and how it organizes data • Determined how tables are structured to properly store data in a relational database

More Related