Chapter 4 Chapter Relational DB - Copy.pptx

relationl database -marchal book

1. LEARNING OBJECTIVES What is a database system, and how does it differ from file-oriented systems? What is a relational database system? How do you design a well-structured set of tables in a relational database? How do you query a relational database system?

2. Databases and Files What is the nature of data stored in an accounting system For financial reporting, an accounting system must necessarily store data about : Assets Liabilities and equity revenue and expense transactions. audit, tax, and control information needs. information about budgets, transactions in progress

3. File-Oriented Systems In early processing systems, an organization’s information was stored as groups of records in separate files. These file processing systems constituted of a few data files and many application programs. Each file called a flat file contained and processed information for one specific function, such as accounting or inventory.

4. Limitations of File Processing System: Separated and Isolated Data : To make a decision, a user might need data from two separate files. Data Redundancy : Often, the same information was stored in more than one file. the replication of data caused loss of data integrity. no one would know which information was correct. Difficulty in representing data from user’s view: it was difficult to determine relationships between isolated data in order to meet user requirements

5. Database Systems Databases were developed to address the proliferation of redundant data in the files of systems using the file approach. The goal of the database approach is to create an organization-wide database that stores all of the data needed to operate the business while linking data across various functions and eliminating redundancy In summary, a database is an organized collection of data about a set of entities stored with as little data redundancy as possible. A database’s value comes from the consolidation of data into a common pool that can serve a variety of users and data processing applications.

6. The Advantages of Database Systems DBMS offers a variety of techniques to store & retrieve data Uniform administration procedures for data A DBMS uses various powerful functions to store and retrieve data efficiently. Offers Data Integrity and Security The DBMS implies integrity constraints to get a high level of protection against prohibited access to data. Reduced Application Development Time Consume lesser space Reduction of redundancy. Data independence.

7. A database Management System (DBMS) is the program that manages and controls the data and the interfaces between the data and the application programs that use the data stored in the database The database administrator (DBA) is responsible for coordinating, controlling, and managing the database

8. LOGICAL AND PHYSICAL VIEWS OF DATA The logical view is how people conceptually organize and understand the relationships among data items. For example : a sales manager views all customer information as being stored in a table. The physical view refers to the way data are physically arranged and stored in the computer system. The DBMS allows users to access, query, or update the database without reference to how or where data are physically stored. Separating the logical and physical views of data also means that users can change their logical view of data without changing the way data are physically stored The Database approach provides two separate views of the data: the physical view and the logical view

9. A schema

10. Physical Schema It is also known as  Internal level. This is the  lowest level  of the architecture. This data is stored in the external hard drives in the form of bits. This level describes how the data actually stored in the storage device. This physical level also discuess encryption techniques. This level is also responsible for allocating space of the data.

11. Conceptual Schema It is also known as  logical level. The conceptual level is a higher level than the physical level. This level is maintained by DBA(database administrator). The whole design of the database such as relationship among data ,schema of data etc.are described in this level. It is the  middle level  between external or internal levels.

12. External Schema It is also known as  view level . This is  highest level  architecture and closest to the user. The user doesn’t need to know the database schema details such as data structure, table definition etc. External level is related to the data which is viewed by individual end users.

13. A data dictionary A data dictionary can be defined as  a collection of metadata such as object name, data type, size, classification, and relationships with other data assets. A data dictionary acts as a reference guide on a dataset.

14. DBMS Language A DBMS has several languages : The data definition language (DDL) : builds the data dictionary creates the database describes logical views for each user, and specifies record or field security constraints Examples of DDL commands in structured query language (SQL) would be the CREATE, DROP, and ALTER statements.

15. DBMS Language A DBMS has several languages : The data manipulation language (DML) changes database content, including data, updates, insertions, and deletions SQL examples would include INSERT, UPDATE, TRUNCATE, and DROP statements

16. DBMS Language A DBMS has several languages : The data query language (DQL) is a high-level, English-like language that contains powerful, easy-to-use commands that enable users to retrieve, sort, order, and display data. SQL examples the SELECT statement in the SQL language

17. R elational data model A two-dimensional table representation of data; each row represents a unique entity (record) and each column is a field where record attributes are stored Inventory Relation Attribute/Column Name Row/Record /Tuple Filed

18. Type Of Attribute

19. Simple Attributes   T he Student is an entity represented by a rectangle I t consists of attributes:   Roll_no , class, and Age.  W e can't further subdivide the Roll_no attribute and even the other two attributes into sub-attributes . These attributes are also known as  atomic attributes

20. Composite Attributes   T he Student is an entity represented by a rectangle I t consists of attributes:   Roll_no , class, and Age.  W e can't further subdivide the Roll_no attribute and even the other two attributes into sub-attributes . These attributes are also known as  atomic attributes

21. Basic Requirements Of A Relational Database We now turn to the guidelines used to develop a properly structured relational database . . Every column in a row must be single valued Primary keys cannot be null. Foreign keys, if not null, must have values that correspond to the value of a primary key in another table All nonkey attributes in a table must describe a characteristic of the object identified by the primary key These four constraints produce a well-structured (normalized) database in which data are consistent and data redundancy is minimized and controlled.

22. Two Approaches to Database Desgine normalization begins by assuming that everything is initially stored in one large table. Rules are then followed to decompose that initial table into a set of tables that are called third normal form (3NF) semantic data modeling the designer uses knowledge of business processes and information needs to create a diagram that shows what to include in the database. This diagram is used to create a set of relational tables that are already in 3NF

23. Database Systems And The Future of Accounting Database systems expand accounting’s ability to produce real-time dynamic reports of all aspects of the accounting equation Databases are capturing increasing amounts of transaction data beyond what was captured through accounting journals and ledgers in double entry accounting. Presently, data about even the smallest movements of materials in production, inventory defects, machine operations, and employee effort can be captured in real time. Management can access the information they need whenever they want it. For example, tables storing information about assets can include historical costs as well as current replacement costs, market values, and real-time utilization

24. Chapter Quiz What is each row in a relational database table called? a. tuple b. relation c. attribute d. anomaly An employee database that consolidates payroll, personnel, and job skills master files is referred to as a. data integration . b. data sharing. c. data independence.

25. Chapter Quiz Which of the following describes a situation where non-primary key items are stored multiple times, leading to data inconsistencies? a. update anomaly b. insert anomaly c. delete anomaly The internal-level schema of a database system consists of an individual user’s view of portions of a database and is also called a subschema. a. True b. False

26. Chapter Quiz Which of the following is an individual user’s view of the database? a. internal-level schema b. conceptual-level schema c . external-level schema d. logical-level schema Which of the following would managers most likely use to retrieve information about sales during the month of October? a. DQL b. DML c. DSL d. DDL

27. Chapter Quiz Which of the following is not considered an advantage of a database system? a. Data are independent of the programs that use them. b. Integrated data can be shared with authorized users. c. Data can be stored multiple times in multiple locations . d. Master files are consolidated and accessible to many application programs. The constraint that all foreign keys must have either null values or the value of a primary key in another table is referred to as which of the following? a . referential integrity rule b. entity integrity rule c. foreign key value rule d. null value rule

28. Chapter Quiz The constraint that all primary keys must have non-null data values is referred to as which of the following? a. referential integrity rule b. entity integrity rule c. normalization rule d. relational data model rule Which of the following is not a basic requirement of a relational database? a. Primary keys cannot be null. b. Every column in a row must be single valued. c. Foreign keys cannot be null. d. All non-key attributes in a table must describe a characteristic of the object identified by the primary key