database management system.pptx
DESCRIPTION
Database ManagementTRANSCRIPT
![Page 1: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/1.jpg)
Database Management System
Group 1
![Page 2: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/2.jpg)
![Page 3: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/3.jpg)
Flat-File Versus Database Environments
Computer processing involves two components: data and instructions (programs).
Conceptually, there are two methods for designing interface between programs and data: file-oriented (flat-file) processing: A specific data
file was created for each application [legacy systems]
data-oriented processing: Create a single data repository to support numerous applications [current systems]
Disadvantages of file-oriented processing include redundant data and programs and varying formats for storing the redundant data.
![Page 4: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/4.jpg)
File-Oriented (Flat-File) Environment
Program 1
Program 2
Program 3
A,B,C
X,B,Y
L,B,M
User 2 (A/R)Transactions
User 1(Sales)Transactions
User 3 (Payroll)Transactions
Data
Where B is a customer record. Assume B’s address changes. Each userthat works with the B record must modify the address, increasing chance
for error.
![Page 5: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/5.jpg)
Database Approach
Program 1
Program 2
Program 3
User 2 (A/R)Transactions
User 1 (Sales)Transactions
User 3 (Payroll)Transactions
Database
A,B,C,X,Y,L,M
With the database approach, any User could change B’s address - it would be
changed for all users. changes. [Assume all users have authority to change the
address for this example.]
DBMS
![Page 6: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/6.jpg)
Data Redundancy & Flat-File ProblemsData Storage - creates excessive storage
costs of paper documents and/or magnetic form
Data Updating - any changes or additions must be performed multiple times (see example of B’s address)
Currency of Information - potential problem of failing to update all affected files
Task-Data Dependency - user’s inability to obtain additional information as his or her needs change
![Page 7: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/7.jpg)
Data sharing/centralized database resolves flat-file problems:
No data redundancy - Data stored only once, eliminating data redundancy and reducing storage costs.
Single update - Because data are in only one place, requires only single update procedure, reducing time/cost of keeping database current.
Current values - Change to database made by any (authorized) user yields current data values for all other (authorized) users.
Task-data independence - As users’ information needs expand beyond immediate domain, new needs can be more easily satisfied than under flat-file approach
![Page 8: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/8.jpg)
The Database Management System
Three conceptual mode Hierarchical Model Network Model Relational Model
Elements of Database Management System Users Database Management System
Data Definition Language Data Manipulation Language Query Language
Database Administrator
![Page 9: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/9.jpg)
Database Management SystemFeatures: Program development Back up and recovery Database usage reporting Database access
![Page 10: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/10.jpg)
Data Definition Language Internal view
Conceptual view (schema)
User view (sub schema)
![Page 11: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/11.jpg)
DBMS Operation A user program sends a request for data
to DBMS. The DBMS analyzes the request by
matching the called data elements against the user view and the conceptual view.
The DBMS determines the data structure parameters from the internal view and passes them to the operating system, which performs the actual data retrieval.
![Page 12: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/12.jpg)
Using the appropriate access method, the operating system interacts with the disk storage device to retrieve the data from the physical database.
The operating system then stores the data in a main memory buffer area managed by the DBMS.
![Page 13: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/13.jpg)
The DBMS transfer the data to the user’s work location in main memory. At this point, the user’s program is free to access and manipulate the data.
When processing is complete. Steps 4, 5, and 6 are reversed to restore the processed data to the database.
![Page 14: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/14.jpg)
Data Manipulation Language
Is the proprietary programming language that a particular database model uses to retrieve, process, and store data
![Page 15: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/15.jpg)
Structured Query Language
SQL is a non-procedural language with many commands that allow users to input, retrieve, and modify data easily.
Basic commands:SELECT –get data from a database tableUPDATE – change data in a database tableDELETE – remove data from a database
tableINSERT INTO – insert new data in a
database table
![Page 16: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/16.jpg)
![Page 17: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/17.jpg)
Data Dictionary
Describes every data element in the database
This enables all users to share a common view of the data resource and greatly facilitates the analysis of user needs
![Page 18: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/18.jpg)
![Page 19: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/19.jpg)
Physical Database
is a representation of a data design which takes into account the facilities and constraints of a given database management system.
![Page 20: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/20.jpg)
![Page 21: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/21.jpg)
Relational Database Model
is a digital database whose organization is based on the relational model of data, as proposed by E.F. Codd in 1960. This model organizes data into one or more tables (or "relations") of rows and columns, with a unique key for each row.
![Page 22: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/22.jpg)
Each row in a table has its own unique key, rows in a table can be linked to rows in other tables by storing the unique key of the row to which it should be linked (where such unique key is known as a "foreign key").
![Page 23: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/23.jpg)
![Page 24: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/24.jpg)
![Page 25: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/25.jpg)
Relational Database Concepts
![Page 26: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/26.jpg)
Entity, Occurrence, and Attributes
Entityanything about which the
organization wishes to capture data.
Two kinds of entity:PhysicalConceptual
![Page 27: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/27.jpg)
Entity, Occurrence, and Attributes
Data Model- is the blueprint for ultimately creating the physical database.
Entity Relation (ER) Diagram- graphical representation used to depict the model.
![Page 28: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/28.jpg)
Data Model using ER Diagram
Customer
Product
Payment
Sen
ds
Buys
![Page 29: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/29.jpg)
Entity, Occurrence, and Attributes
Occurrence- is used to describe the number of instances or records that pertain to a specific entity.
Attributes- are the data elements that define an entity.
![Page 30: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/30.jpg)
Associations and Cardinality
The labeled line connecting two entities in a data model describes the nature of the association between them.
![Page 31: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/31.jpg)
Associations and Cardinality
Cardinality- is the degree of association between two entities.
4 basic forms:• 0, 1• 1,1• 0,M• 1,M
![Page 32: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/32.jpg)
Cardinality Notation
Crow’s footWrite the cardinal values on
each end of the association line connecting the two entities
![Page 33: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/33.jpg)
Physical Database TablesFour characteristics of properly designed tables:• The value of at least one attribute in each
occurrence must be unique, which is the primary key. Other attributes need not to be unique.
• All attribute values in any column must be of the same class.
• Each column in a given table must be uniquely named. However, different tables may contain columns with the same name.
• Tables must conform to the rules of normalization.
![Page 34: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/34.jpg)
Linkages Between Relational Tables
Foreign keys- physically connect related tables to achieve the associations described in the data model.
![Page 35: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/35.jpg)
Linkages Between Relational Tables
User Views- the set of data that a particular user sees.
Two types of views Two kinds of views:
- Digital - Simple - Physical - Complex
![Page 36: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/36.jpg)
Anomalies, Structural Dependencies, and Data
Normalization
![Page 37: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/37.jpg)
Database Anomalies
First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)
![Page 38: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/38.jpg)
Three Types of Anomalies(anomalies are found in Unnormalized tables)
• Update Anomaly: Modification on attribute must be made in each of rows in which attribute appears.
• Insertion Anomaly: New item cannot be added to table until at least one entity uses particular attribute item.
• Deletion Anomaly: If attribute item used by only one entity is deleted, all information about that attribute item is lost.
• Anomalies can be corrected by creating relational tables.
![Page 39: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/39.jpg)
![Page 40: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/40.jpg)
The database anomalies described are symptoms of structural problems within tables called dependencies.
repeating groupspartial dependenciestransitive dependencies
Normalizing Tables
![Page 41: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/41.jpg)
![Page 42: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/42.jpg)
Normalization Process involves identifying and removing structural dependencies from tables under review.
![Page 43: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/43.jpg)
Linking Normalized Tablesif 1:1 association, either of table’s
primary keys may be foreign key.if 1:M association, primary key of the
ONE side is embedded as foreign key in the MANY side.
if M:M (M:N) association, create separate linking table.
![Page 44: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/44.jpg)
![Page 45: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/45.jpg)
![Page 46: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/46.jpg)
Accountants and Data Normalization
![Page 47: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/47.jpg)
Accountants and Data Normalization
• Data Normalization – Technical matter– Responsibility of systems professionals
• Update anomaly – generate conflicting and obsolete database values
• Insertion anomaly – result in unrecorded transactions and incomplete audit trails
• Deletion anomaly – cause the loss of accounting records and the destruction of audit trails
![Page 48: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/48.jpg)
Accountants and Data Normalization
• Accountants– Should have an understanding of
the process and be able to determine whether a table is properly normalized
![Page 49: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/49.jpg)
DESIGNING RELATIONAL DATABASES
• “ View Modeling” – six phases of database design
1. Identify Entities2. Construct a data model showing entity
associations3. Add primary keys and attributes to the model4. Normalize the data model and add foreign
keys5. Construct the physical database6. Prepare the user views.
![Page 50: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/50.jpg)
1. Identify Entities• To pass as valid entities, two
conditions need to be met:
1. An entity must consist two or more occurrences.
2. An entity must contribute at least one attribute that is not provided through other entities.
![Page 51: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/51.jpg)
Purchasing system1. The purchasing agent reviews the inventory status
report for items that need to be reordered.
2. The agent selects a supplier and prepares online purchase order.
3. The agents prints a copy of the purchase order.
4. The supplier ships inventory to the company. Upon its arrival, the receiving clerk inspects the inventory and prepares an online receiving report. The computer system automatically updates the inventory records.
![Page 52: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/52.jpg)
2. Construct a Data Model Showing Entity Associations
Determine the associations between entities and document them with and ER diagram.
Associations represent business rules.
![Page 53: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/53.jpg)
2. Construct a Data Model Showing Entity Associations
Receiving Report
Purchase Order
Supplier
InventoryUpdates Contains
Sent to
Is associated
with
Supplies0,
M:M
M:M
0, M:M
1: 0, M
1: 1
![Page 54: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/54.jpg)
Add Primary Keys and Attributes to the Model
![Page 55: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/55.jpg)
![Page 56: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/56.jpg)
![Page 57: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/57.jpg)
![Page 58: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/58.jpg)
![Page 59: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/59.jpg)
Prepare the user viewsnormalize tables should support
all required views of system users
user views restrict users from have access to unauthorized data
![Page 60: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/60.jpg)
Global View Integration• View Integration is combining
the data needs of all users into a single schema or enterprise-wide view.
![Page 61: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/61.jpg)
Database in a Distributed Environment
• Centralized Databases- remote users send requests via
terminals for data to the central site, which processes the requests and transmits the data back to the user.
- the central site performs the functions of file manager that services the data needs of the remote users.
![Page 62: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/62.jpg)
Data Currency in a DDP Environment
- During data processing, account balances pass through a state of temporary inconsistency, in which their values are incorrectly stated.
- To Illustrate, consider the computer logic for recording the credit sale of P2,000 to customer James
INSTRUCTION DATABASE VALUESAR-Jones AR-Control
START1. Read AR-Sub account (Jones) 1,5002. Read AR-Control account 10,0003. Write AR-Sub account (Jones) +2,000 3,5004. Write AR-Control account +2,000 12,000END
![Page 63: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/63.jpg)
- To achieve data currency, simultaneous access to individual data elements by multiple sites needs to be prevented
- Software control (usually a function of the DBMS) that prevents multiple simultaneous accesses to data.
Data Lockup
![Page 64: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/64.jpg)
Distributed Databases- It can be distributed using either partitioned
or replicated technique
• Partitioned Database- Splits the central database into segments or
partitions that are distributed to their primary users. The advantage of this approach are:
1. Storing data at local sites increases users’ control2. Permitting local access to data and reducing the
volume of data that must be transmitted between sites improves transaction processing response
time3. Reduce the potential for disaster
![Page 65: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/65.jpg)
• Replicated Databases
- Effective in companies in which there exists a high degree of data sharing but no primary user.
- Data traffic between sites is reduced considerably
- Supports read-only queries
• Concurrency Control
- The presence of complete and accurate data at all remote sites.
- Systems designers need to employ methods that transactions processed at each site are accurately reflected in the databases at all other sites
![Page 66: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/66.jpg)
Distinguishing features of Structured and
Relational databases
![Page 67: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/67.jpg)
![Page 68: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/68.jpg)
![Page 69: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/69.jpg)
![Page 70: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/70.jpg)
![Page 71: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/71.jpg)
![Page 72: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/72.jpg)
![Page 73: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/73.jpg)
![Page 74: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/74.jpg)
![Page 75: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/75.jpg)
![Page 76: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/76.jpg)
![Page 77: Database Management System.pptx](https://reader034.vdocuments.us/reader034/viewer/2022052701/563dbad7550346aa9aa87d20/html5/thumbnails/77.jpg)