Managing Enterprise Database ISYS 464 David Chao Introduction
Managing Enterprise Database ISYS 464 David Chao
Introduction to Databases • The most important component of an information system • Created to support all levels of business operations: – Day-to-day operations • Transaction support systems – Decision-makings • Business intelligence, data warehouse, Big Data – Strategic plans • Enabling new services, new business operations
Data File and Database • A data file is a computer file which stores data to be used by a computer application or system. – Data: stored representations of meaningful objects and events. – Example: A student fie to store students’ information: id, name, address. • Database: A group of logically related files – Example: A database with student file and course file.
Many Types of Data File • Pure text file: – File contains data only • CSV file (Comma-Separated Values). – File contains data and meaning of the data • XML file • JSON file • A proprietary file: A file that contains data that is ordered and stored according to a particular encoding-scheme, designed by a company.
Example of CSV "s 1", "peter", 3 "s 2", "paul", 2. 5 "s 3", "mary", 3. 5 "s 5", "smith", 3. 5 "s 7", "chen", 3. 5 "s 9", "John", 3. 2 "S 4", "Nancy", 2. 5 Note 1: We don’t know the meaning of the data. Note 2: Every software development tool is capable of reading this file.
Example of XML File Data with meaning <employees> <employee> <first. Name>John</first. Name> <last. Name>Doe</last. Name> </employee> <first. Name>Anna</first. Name> <last. Name>Smith</last. Name> </employee> <first. Name>Peter</first. Name> <last. Name>Jones</last. Name> </employees>
Example of JSON File {"employees": [ { "first. Name": "John", "last. Name": "Doe" }, { "first. Name": "Anna", "last. Name": "Smith" }, { "first. Name": "Peter", "last. Name": "Jones" } ]} Note: Data is represented as collection of objects with properties.
Database Files • Examples: – MS Access: . accdb file – MS SQLServer: . mdf – My. SQL: . db • Note: You cannot just double-click the file to open it.
Database Systems Maintain Metadata • System catalog(data Dictionary): Database Systems use system catalog to maintain detailed description of files and fields in the database.
Table 1 -1 Example Metadata for Class Roster • Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and data context Name Type Length Min Description Source Course Alphanumeric 30 Blank Course ID and name Academic Unit Section Integer 1 1 9 Section number Registrar Semester Alphanumeric 10 Blank Semester and year Registrar Name Alphanumeric 30 Blank Student name Student IS ID Integer 9 Blank Student ID (SSN) Student IS Major Alphanumeric 4 Blank Student major Student IS GPA Decimal 3 0. 0 4. 0 Student grade point average Academic Unit imum Max imum Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Structured vs Unstructured File • Structured: Fixed format record – every record in a file has identical fields and data types. • Unstructured: varying format – Records in a file may have different fields and data types.
Example of Structured File
Example of Unstructured File {"employees": [ { "first. Name": "John", "last. Name": "Doe" }, { "first. Name": "Anna", "last. Name": "Smith" }, { "first. Name": "Peter", "last. Name": "Jones" } { “last. Name": “Boxer", “Address": “ 123 first Ave" , “Income”: 95000. 00”} ]} Note 1 : The last record has different fields. Note 2: JSON and XML can handle unstructured data.
Different Database Systems • Relational database systems – Structured format • Object-relational database systems – Unstructured format – My. SQL, Oracle, SQL Server • Document database systems – Unstructured format – Mongo. DB
Example of My. SQL JSON Data Type
Information • Data processed to increase knowledge in the person using the data.
Figure 1 -1 Converting Data to Information (1 of 2) (a) Data in context Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Figure 1 -1 Converting Data to Information (2 of 2) (b) Summarized data Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Traditional File-Based Data Processing • A collection of application programs that perform services for the end-users. Each program defines and manages its own data.
Example of File Processing • Student file with data: SID, Sname, and GPA • Assuming the file is stored in a CSV file (Comma. Separated Values). • Sample data: – “S 5”, ”Peter”, 3. 0 – “S 1”, “Paul”, 2. 5 • Questions: – How many students? – What is average GPA?
Sequentially Accessing the Student File to Compute Average GPA Dim file. Number, st. Counter As Integer Dim SID, SNAME As String Dim gpa, sum. Gpa As Double file. Number = Free. File() File. Open(file. Number, "c: stdata. txt", Open. Mode. Input) Do While Not EOF(file. Number) Input(file. Number, SID) Input(file. Number, SNAME) *** program defines and manages its own data*** Input(file. Number, gpa) sum. Gpa += gpa st. Counter += 1 Loop Message. Box. Show(sum. Gpa / st. Counter. To. String)
Example: COBOL Program Data Division: FD Input-File. LABEL RECORDS ARE OMITTED DATA RECORD IS Input-Record. 01 Input-Record 05 Name-In PIC X(20). 05 Address-In PIC X(25). 05 City-In PIC X(15). 05 State-Zip-In PIC X(20).
Major Disadvantage of File Processing: Program-Data Dependence • All programs maintain metadata for each file they use. An application program includes in its source code the structural definition of that data it processes. . • Program-data dependence: The extent to which a program is exposed to changes made to that data source is called data dependence. • Example: If student’s major is added to the student data, the application program must change its code. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Figure 1 -2 Old File Processing Systems at Pine Valley Furniture Company Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Other Disadvantages of File Processing • Duplication of Data – Different systems/programs have separate copies of the same data – Inconsistent data • Fixed queries – No facilities for asking unplanned, ad hoc queries • Each application program must have its own processing routines for reading, inserting, updating, and deleting data • Limited Data Sharing – No centralized control of data Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
The Database Approach • Data models: Graphical diagram capturing nature and relationship of data – Enterprise Data Model – high-level entities and relationships for the organization ▪ Entity Relationship Diagram – Project Data Model – more detailed view, matching data structure in database or data warehouse • Entities – Person, place, object, event, or concept – Composed of attributes • Relationships: Interaction between entities – One-to-one, one-to-many, many-to-many Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Figure 1 -3 Comparison of Enterprise- and Project-Level Data Models (a) Segment of an enterprise data model (b) Segment of a project data model Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Database Approach • The database holds not only the data but also a description of the data. – System catalog (data dictionary, or metadata) • A central location where data descriptions are stored. • Data about data – Program-data independence: Application program not affected by data source change. • Demo: VS Program Data Independence Demo – Web. Application 1, Univ. DB. mdb, mystudent table
ASP. Net Program Data Independence Demo: protected void Page_Load(object sender, Event. Args e) { string str. Conn = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=C: \Home. Page\Univ. DB. mdb"; Ole. Db. Connection obj. Conn = new Ole. Db. Connection(str. Conn); string str. SQL = "select * from my. Student; "; Ole. Db. Command obj. Comm = new Ole. Db. Command(str. SQL, obj. Conn); obj. Conn. Open(); Ole. Db. Data. Reader obj. Data. Reader; obj. Data. Reader = obj. Comm. Execute. Reader(); Grid. View 1. Data. Source = obj. Data. Reader; Grid. View 1. Data. Bind(); } Note: This statement not affected by table structure change: string str. SQL = "select * from my. Student; ";
Advantages of the Database Approach • Program-data independence – The separation of data descriptions from the application programs that use the data. – Allows the data to change without changing the application programs. • Improved data consistency: – Database systems enforce data integrity rules. • Improved data sharing • Support Ad Hoc queries
Database Management System (DBMS) • A software that enables users to define, create, maintain, and control access to the database. – Data Definition Language (DDL) • System catelog – Data Manipulation Language (DML) – Control access: • Security, integrity, concurrent access, recovery, support for data communication, etc. – Utility services • File import/export, monitoring facilities, etc. • Powerful query language to support Ad Hoc queries.
Database Management System • A software system that is used to create, maintain, and provide controlled access to user databases Order Filing System Invoicing System Payroll System DBMS Central database Contains employee, order, inventory, pricing, and customer data DBMS manages data resources like an operating system manages hardware resources
Evolution of DB Systems
Relational DBMS Examples • • • Microsoft: MS Access, SQL Server Oracle: My. SQL, Oracle IBM: DB 2 SAP: HANA Column oriented relational db Etc. Note: These systems now all extended to support objects.
Relational DBMS Demo using MS Access • Every table must have a primary key. • Related records in tables are linked by using key (foreign key). • Querying database using: – Query design view – Structured Query Language
Database Schema • External Schema – User Views – Subsets of Conceptual Schema • Conceptual Schema – This level describes what data is stored in the database and the relationships among the data. – View of the data administrator – E-R models • Internal schema – Logical Schema: • Detailed table design – Physical Schema • File organizations, indexes
Three-schema architecture Different people have different views of the database…these are the external schema The internal schema is the underlying design and implementation
Database Data Independence • Data independence means that upper levels are unaffected by changes to lower levels. – Logical data independence • Changes to the conceptual level, such as the addition of new entities, attributes, or relationships, should be possible without having to change the existing external level design. – Physical data independence • Changes to the physical level, such as using a different file organization, indexes, should be possible without having to change the conceptual level design.
Three-Level Example Employee Entity • Conceptual design: – Employee entity with attributes: Emp. ID, Emp. Name, Date. Of. Birth, Salary, and Sex. • Internal level: – Logical schema: • • • Emp. ID – 4 characters Emp. Name – 30 characters Date. Of. Birth – Date field 8 bytes Salary – Number(7, 2) Sex – 1 character – Physical schema: • Record size = 4 + 30 + 8 + 7 +1 = 50 bytes • Sequential file with index on Emp. ID field • External level(users’ view): • Emp. Age. View: Emp. ID, Emp. Name, Age: Year(Now())Year([Birthdate]) • Emp. Salary. View: Emp. ID, Emp. Name, Salary
Benefits of Using Views • Views provide a level of security. • Views provide a mechanism to customize the appearance of the database. • Views provide a consistent, unchanging picture of the database, even if the underlying database is changed.
Database Application • It is a program that interacts with the database at some point in its execution by issuing an appropriate request (typically an SQL statement) to the DBMS. • Database programming
The Range of Database Applications • Personal databases: – Desktop, PDA/Smart Phone • Workgroup databases • Departmental/divisional databases • Enterprise database – Enterprise Resource Planning (ERP) • Integrate all enterprise functions (manufacturing, finance, sales, marketing, inventory, accounting, human resources) – Data Warehouse • Integrated decision support system derived from various operational databases
Figure 1 -12 Multi-Tiered Client/Server Database Architecture Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved
Four basic operations of database application • CRUD Operations: – Create: add new records – Read: retrieve records – Update: modify records – Delete: delete records • Database servers typically offer API to do CRUD.
MVC: Model/View/Controller Model Example: Employee Class public class Employee { public int Id { get; set; } public string eid { get; set; } public string ename { get; set; } public string sex { get; set; } public decimal salary { get; set; } [Data. Type(Data. Type. Date)] public Date. Time hiredate { get; set; } public string deptid { get; set; } } Demo: Visual Studio Net. Core. MVC project.
Generated View to Enter Data https: //localhost: 44358/Employees/Create
Generated View to Edit/Delete Data https: //localhost: 44358/Employees
Major Database Management Activities • Creating database • Updating database • Querying database
Creating Database • Analysis – System analysis • Data Flow Diagram • Data modeling • Entity Relation Diagram, ERD • Design – Maps the data model on to a target database model. – Relational/object-relational/document model • Implementation: Efficiently store and retrieve data – File organization and index
Updating Database • Insertions, deletions, modifications – Update pattern: • Allow update, Read only, Insertion only, no deletion, etc. • Concurrent processing – Database Read/Write operations – Multiple users • Transaction management
Querying Database • Relational operators – Selection, projection, join, etc. • SQL – Structured Query Language
New Developments in Database • Storage media: In-Memory, Cloud • New Database systems: – Object-Relational database, No. SQL(Not Only SQL) document database • Business Intelligence: – Data warehouse, Big Data Analytics • Types of data: – JSON (Java Script Object Notation) data, CSV (comma separated values), geospatial data, XML etc.
Example of JSON Employee with dependents: { "employee": { “eid”: ”e 1”, "name": "John", "age": 30, "city": "New York" }, "dependents": [{"did": "d 1", "dname": "peter"}, {"did": "d 2", "dname": "paul"}] }; Note: JSON is supported by object-relational systems such as My. SQL 8, and document database.
Example of Geo. Spatial Data POINT(X, Y): where X and Y is latitude and longitude value respectively.
Course Overview • An introduction to the design and use of database: – Creating database: • Analysis: Data modeling, ERD, Normalization • Design: Relational database design • Implementation: – Use SQL data definition language to create database – Updating database: • SQL data manipulation language – Querying database: • SQL Select commands • Database management technologies: – – – Improving query performance with index Object relational database, managing JSON documents No. SQL with Mongo. DB Data warehouse Database security and management
Download My. SQL 8 Community Edition • Free download for Windows at: https: //dev. mysql. com/downloads/installer/ And choose: mysql-installer-community-8. 0. 21. 0. msi • Free download for Mac OS: https: //dev. mysql. com/downloads/mysql/ – And select Mac OS.
- Slides: 56