Ch1-Introduction-Database Planning.pdf

Database Systems 1 INFORMATION MODELS AND SYSTEMS 18/01/2021  Data as a Resource 2 — A resource is any asset that is of value to an organization and that incurs costs. — Many org...

Database Systems 1 INFORMATION MODELS AND SYSTEMS 18/01/2021  Data as a Resource 2 — A resource is any asset that is of value to an organization and that incurs costs. — Many organizations, such as banks and brokerage houses, are heavily dependent on data and would fail very quickly if their data were lost. — Resources include capital equipment, financial assets, personnel, and data — Database is a resource because: 1. Operational data has value 2. Database incurs cost 3. Professionally managed by DBA 18/01/2021  Characteristics of Data 3 Data vs. information Data: raw facts Example: printout of tables as they are stored, without headings saying what they mean Information: processed data, useful for decision-making Example: formatted report using database 18/01/2021  What is Data Model 4 — Data Model is a collection of conceptual tools for describing data, data relationships, data semantics and consistency constraint. — A data model is a conceptual representation of data structures required for data base and is very powerful in expressing and communicating the business requirements. — A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. 18/01/2021  Data Model 5 — A data model provides a way to describe the design of a database at the physical, logical and view levels. — There are three different types of data models produced while progressing from requirements to the actual database to be used for the information system 18/01/2021  Four Levels of Data 1. Real world Enterprise in its environment Mini-world, or Universe of Discourse – part of the world that is represented in the database 2. Conceptual Model Entities, entity sets, attributes, relationships Often represented as ER, EER or UML diagram 3. Logical model of database-intension Metadata, data about data Record types, data item types, data aggregates Schema - stored in data dictionary, catalog 4. Data occurrences-extension Database itself Data instances files  Four Levels of Discussing Data Realm Objects Examples Real World Enterprise Corporation, university, bank Containing Miniworld Some aspects of the Human resources enterprise Student enrollment Customers and accounts Conceptual Model Entitty a student, a class Attribute name, schedule Entity set all students, all classes Relationship Student entity relates to class entity by being enrolled in it Logical Model Record type Student record type, Class record type Metadata: data definitions, stored in Data item type stuid, classNumber Data Dictionary Data Aggregator address, consisting of street, name, state, ZIP Data Student record occurrence Record of student Tom Smith Occurrences stored in the database Data item occurrence ‘S1001’, ‘Smith’, ‘Tom’,’History’,90 File Student file with 5000 Student records Database University database containing Student file, Class file, Faculty file, …  Data Sublanguages — DBMS uses a data sublanguage, with two parts ¡ Data definition language (DDL) - used to define the database ¡ Data manipulation language (DML) - is used to process the database ¡ These languages are called sublanguages because they have limited functionality, lacking many features of general-purpose programming languages. ¡ Data sublanguage may be embedded in a host language-general programming language,such as Java, C, C++, C#, COBOL..  Characteristics of a Conceptual Database Model 1- ¡ Faithfully mirrors the operations of the organization 2- ¡ Flexible enough to allow changes as new information needs arise 3- ¡ Supports many different user views 4- ¡ Independent of physical implementation 5- ¡ Does not depend on the model used by a particular database management system  Stages in Database Design Analyze User Environment Develop Conceptual Model 1- — Analyze user environment 2- — Develop conceptual data Choose DBMS model 3-— Choose a DBMS Develop Logical Model 4-— Develop logical model, by mapping conceptual Develop Physical Model model to DBMS 5-— Develop physical model Evaluate Physical Model 6-— Evaluate physical model 7- — Perform tuning, if Tune System indicated 8-— Implement physical model Implement System  Analyze user environment 11 — The first step in designing a database is to determine the current user environment. It means that The designer should study all current applications, and determin their input and output, he also should examine all reports generated by the present system, and interviews users to determine how they use the system. — The result of this analysis is a description of the user environment and requirements. 18/01/2021  Identifying User Requirements. 12 — The DBA or designer works with present and potential users to design new reports and new transactions, after examining transactions and all reports generated by the present system and consults with users to determine whether they satisfy their information needs. 18/01/2021  Develop a conceptual data model. 13 — The designer develops a detailed conceptual model of the database— identifying the entities, attributes, and relationships tamong them. — The database designer must be able to identify, classify, and structure elements in the design. — The designer can look at different levels of abstraction, so that an abstract object on one level becomes a component of a higher-level abstraction. — The designer gradually comes to understand the workings of the organization and the meaning of its data, and expresses that understanding in the chosen model. — The designer has to consider how the database is to be used. The types of applications and transactions, the kinds of access, the volume of transactions, the volume of data, the frequency of access, and other quantitative data must be specified. 18/01/2021  Designing the Conceptual Model 14 — The DBA or designer identifies all entities, attributes, and relationships that are to be represented in the database, and develops a conceptual model that is an accurate reflection of the mini-world. — We identify entities, which are persons, places, events, objects, or concepts about which we collect data. — We group similar entities into entity sets. — For example, for the set of all customers, we form the entity set we might call Customers. Similarly, we might have entity sets called Employees, Students, Accounts, Investments, Classes, and Patients—each consisting of all entity instances of the corresponding type. — A conceptual model may have many entity sets. ¡ For the Student entity set, attributes might include studentId, name, address, telephoneNumber, major, creditsPassed, gpa, and adviser. — An ER or UML diagram is usually produced at this stage. 18/01/2021  Choose a DBMS 15 — The designer uses the specifications and knowledge of available hardware and software resources to evaluate alternative database management systems and chooses the system that best satisfies the specifications. — The DBA considers the conceptual model and other database specifications and the hardware and software available. — The DBA Chooses the DBMS that best fits the environment and meets the specifications. 18/01/2021  Develop the logical model. 16 — The structure of the database. — The logical model is also called the intension of Database. — database schema is a written description of the logical model. ¡ Schema evolution : is the process of changing the DB schema when new data needs arise — Metadata, data about data. — Record type contains several data item types, each of which represents an attribute of an entity. ¡ For example the Student record type, the data item types could be stuId, stuName, address, phone, major, credits, gpa, and adviser. — A data item (element, field, or attribute ) :is the smallest named unit of stored data ¡ Data items are sometimes grouped together to form data aggregates, which are named groups of simpler data items within a record. ¡ For example, stuName may be a data aggregate that could consist of the simple data items firstName and lastName. Data aggregates allow us to refer to the group of data items as a whole (e.g., stuName) or to the individual items in the group (e.g., lastName). 18/01/2021  Develop the logical model 17 — A record is a named collection of related data items and/or data aggregates. — Relationships must also be represented in the logical model. — The designer maps the conceptual model to the database model used by the chosen DBMS, creating the logical model. 18/01/2021  Develop the physical model. 18 — The designer plans the layout of data, considering the structures supported by the chosen DBMS and the hardware and software resources available. 18/01/2021  Evaluate the physical model. 19 — The designer estimates the performance of all applications and transactions, considering : ¡ The quantitative data previously identified, ¡ The performance characteristics of hardware and software to be used, — Prototype is implementing a selected portion of the database so that user views can be validated and performance measured more accurately. 18/01/2021  Perform tuning if indicated by the evaluation. 20 — Adjustments such as modifying physical structures or optimizing software can be done to improve performance. 18/01/2021  Implement the physical model. 21 — If the evaluation is positive, the designer then implements the physical design and the database becomes operational. 18/01/2021  Design Tools — Data dictionary — Project management software  Data Dictionary — Contains metadata — Can be integrated (system catalog, part of DBMS) or free- standing — Both useful for ¡ Collecting information about data in central location ¡ Securing agreement on meanings of items ¡ Communicating with users ¡ Identifying inconsistencies – synonyms and homonyms ¡ Keeping track of changes to DB structure ¡ Determining impact of changes to DB structure ¡ Identifying sources of/responsibility for items ¡ Recording external/logical/physical models & mappings ¡ Recording access control information ¡ Providing audit information — Catalog also provides audit information  Project Management Software — Tools to help plan and manage projects, especially those with many people — Include several types of charts and graphs ¡ GANTT chart ¡ PERT chart — User specifies ¡ Scope and objectives ¡ Major tasks and phases ¡ Task dependencies ¡ Resources, including personnel ¡ Timelines — Software can ¡ Generate calendars ¡ Produce graphs with different views of project ¡ Provide means of communication for staff Gantt Chart for The Art Gallery Database Project Jan 4 2004 Jan 11 2004 Jan 18 2004 Jan 25 2004 ID Task Name Resource Names Start Finish Duration 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 Define specifications 1/5/2004 1/8/2004 4d 2 Interview users Adam 1/5/2004 1/6/2004 2d Identify transactions, reports 3 Adam, Beth 1/7/2004 1/7/2004 1d needed 4 Begin data dict, x-ref table Beth,Colin 1/8/2004 1/8/2004 1d 5 Create E-R diagram 1/9/2004 1/13/2004 3d Identify 6 Adam 1/9/2004 1/9/2004 4h entities,attributes,relationships Identity cardinality,participation 7 Adam, Beth 1/12/2004 1/12/2004 4h constraints 8 Draw diagram Adam, Colin 1/13/2004 1/13/2004 1d 9 Map E-R to relational model Beth 1/14/2004 1/14/2004 1d 10 Normalize relational model Adam 1/15/2004 1/15/2004 1d 11 Create relational database Colin 1/16/2004 1/19/2004 2d 12 Create EER diagram Adam, Beth, Colin 1/19/2004 1/20/2004 2d Map EER model to object-relational 13 Adam 1/21/2004 1/21/2004 1d model 14 Create object-relational database Beth 1/22/2004 1/23/2004 2d 15 Create UML diagram Beth 1/23/2004 1/26/2004 2d Map UML diagram to object-oriented 16 Colin 1/27/2004 1/27/2004 1d model 17 Create object-oriented database Adam 1/28/2004 1/29/2004 2d  Database Administration 26 — The database administrator is responsible for the design, operation, and management of the database. o Has primary responsibility for planning, designing, developing and managing the operating database o Database designer may do conceptual and logical design; DBA does physical design, implementation, develops, manages system — The DBA must be ¡ technically competent, ¡ a good manager, and a skilled communicator, ¡ and must have excellent interpersonal and communication skills. 18/01/2021  Planning and Design Stage ¡ Preliminary planning ¡ Identifying user requirements ¡ Developing and maintaining the data dictionary ¡ Designing the conceptual model ¡ Choosing a DBMS ¡ Developing the logical model ¡ Developing the physical model  Development Phase ¡ Creating and loading the database ¡ Developing user views ¡ Writing and maintaining documentation ¡ Developing and enforcing data standards ¡ Developing and enforcing application program standards ¡ Developing operating procedures ¡ Doing user training  Database Management Phase — Ensuring Database Security ¡ The DBA must monitor the database to identify vulnerabilities, deal with security threats, and ensure compliance with good security practices. — Monitoring performance ¡ The DBA is responsible for collecting and analyzing statistics on database performance and responding to user complaints and suggestions regarding performance. The running time for applications and the response time for interactive queries should be measured, so that the DBA can spot problems in database use — Tuning and reorganizing ¡ If performance begins to degrade as changes are made to the stored data, the DBA can respond by adding or changing indexes, reorganizing files, using faster storage devices, or optimizing software — Keeping current on database improvements ¡ The DBA should be aware of new features and new versions of the DBMS that become available. ¡ The DBA should keep the DBMS updated.