Lecture 9 Databases (1) PDF

Chapter 4: Organizing and Storing Data Lecture 9: Data Management and Data Modelling  Overview Principals Learning Outcomes 5.1 Data management and Define general data modelling are key aspects of management concepts a...

Chapter 4: Organizing and Storing Data Lecture 9: Data Management and Data Modelling  Overview Principals Learning Outcomes 5.1 Data management and Define general data modelling are key aspects of management concepts and organizing data and information terms, highlighting the advantages and of the database approach to data management 5.2 A well-designed and well- Identify the common functions managed database is central to performed by all database almost all information systems management systems and is an extremely valuable tool in supporting decision- making  Introduction to Databases » Data Data is raw fact or figures or entity. When activities in the organization takes place, the effect of these activities need to be recorded which is known as Data. » Information Processed data is called information The purpose of data processing is to generate the information required for carrying out the business activities.  5.1 Data Management In general Data Management consists of following tasks : Data capture: Which is the task associated with gathering the data as and when they originate. Data classification: Captured data has to be classified based on the nature and intended usage. Data storage: The segregated data has to be stored properly. Data arranging: It is very important to arrange the data properly Data retrieval: Data will be required frequently for further processing, Hence it is very important to create some indexes so that data can be retrieved easily  Metadata (meta data, or sometimes meta information) is "data about data", of any sort in any media.  Manual Filling Systems Prior to the introduction of electronic database systems, almost all of the information an organisation needed to store was organised using manual filing systems Disadvantages of Manual Filing Systems: – The way in which information is organised largely determines the uses to which it can be put. For example, if a list of customers is stored in alphabetical order by name, it becomes difficult to view customers by location. – It is often difficult to retrieve specific items of information quickly. – It might not be possible to add, amend or delete the information held in a manual record without creating a new copy of the record. – If the information is used regularly by a number of different individuals or departments, multiple copies of Manual Paper based Storage  Database » Database – Database may be defined in simple terms as a collection of data – A database is a collection of related data. – The database can be of any size and of varying complexity. – A database may be generated and maintained manually or it may be computerized.  Database The center of almost every information system is a database, used to store data so that it can be processed to provide useful information A database can be used by almost every firm to record history of the firm’s transactions; which can then be used to uncover patters and relationships the firm never considered before, a practice known as data mining The most common type of database is the relational database. So-named because the basic structure for storing data is a table and the word relation is another name for table. A relational database is defined as a series of related tables, stored together with a minimum of duplication to achieve consistent and controlled pool of data  Relational Database A relational database is made up of a number of tables In loose terms, each table stores the data about someone or something of interest to the firm, known as an entity. Customer, Order and Supplier are examples The rows in a table collect all the data about a specific entity. For example in Customer_Table each row stores all the data about one a particular customer-Jane Smith.  These rows are known as records. A table is made up of a number of records The columns in a table are the specific items of data that get stored; for example (name, data of birth, address, etc.)  These columns are known as fields or attributes So a database is made up of tables, which are made up of records, which are made up of fields.  Keys Each record must be unique in some way The primary key is a field that is defined to be unique for each entity Student ID, Exam ID and Library ID are all primary keys that uniquely identify you Primary keys are used to join tables: ‘Post’ a primary key into another table to join the two (see Figure 5.1 and 5.2) Example of Keys  Difference between File system & DBMS » File System 1. File system is a collection of data. Any management with the file system, user has to write the procedures 2. File system gives the details of the data representation and Storage of data. 3. In File system storing and retrieving of data cannot be done efficiently. 4. Concurrent access to the data in the file system has many problems like a. Reading the file while other deleting some information, updating some information 5. File system doesn’t provide crash recovery mechanism. E.g.. While we are entering some data into the file if System crashes then content of the file is lost. 6. Protecting a file under file system is very difficult.  Difference between File system & DBMS DBMS 1. DBMS is a collection of data and user is not required to write the procedures for managing the database. 2. DBMS provides an abstract view of data that hides the details. 3. DBMS is efficient to use since there are wide varieties of sophisticated techniques to store and retrieve the data. 4. DBMS takes care of Concurrent access using some form of locking. 5. DBMS has crash recovery mechanism, DBMS protects user from the effects of system failures. 6. DBMS has a good protection mechanism. » DBMS = Database Management System » RDBMS = Relational Database Management System  Advantages and Disadvantages of using relational database to store datasystem can overcome Due to its centralized nature, the database the disadvantages of the file system-based system Advantages and Disadvantages of using relational database to store data  5.2 Database Management System (DBMS) How do we actually create, implement, use and update a database? Creating and implementing the right database system ensures that the database will support both business activities and goals DBMS: is a group of programs used as an interface between a database and application programs or a database and the user. The capabilities and the types of database systems vary, however but generally they provide the following: Creating and Modifying the Database Storing and Retrieving Data Manipulating Data and Generating Reports  Creating and Modifying the Database Schemas or designs are entered into the DBMS (usually by database personnel) via a data definition language Data definition language (DDL): collection of instructions and commands used to define and describe data and relationships in a specific database – Allows the database’s creator to describe the data and relationships that are to be contained in the schema  Data Dictionary Another important step in creating a database is to establish data dictionary, a detailed description of all the data used in the database. The data dictionary describes all the fields in the database, their range of acceptance values, the type of data (such as alphanumeric or numeric), the amount of storage needed for each and a note of who can access each and who updates each. Stores meta data, a detailed description of all the data used in the database, to achieve the advantages of the database approach in these ways: Reduced data redundancy Increased data reliability Assists program development Easier modification of data and information  Data Dictionary » Example of a data dictionary  Storing and Retrieving Data » One function of the DBMS is to interface between an application program and the database. – When the application program needs data it requests that data through the DBMS – Example: Suppose that to calculate the total price of a new car, a car dealer pricing program needs price data on the engine option-six cylinders instead of the standard four cylinders. – The application program hence requests data from the DMBS; the DBMS working with various system programs, accesses storage devices such as disc drives where the data is stored.  Storing and Retrieving Data » One function of the DBMS is to interface between an application program and the database. – When the application program needs data it requests that data through the DBMS – Example: Suppose that to calculate the total price of a new car, a car dealer pricing program needs price data on the engine option-six cylinders instead of the standard four cylinders. – The application program hence requests data from the DMBS; the DBMS working with various system programs, accesses storage devices such as disc drives where the data is stored.  Figure 5.9 Logical and Physical Access Paths » When the DBMS goes to this storage device to retrieve data it follows a path to the physical location(physical access path) where the price of this option is stored.  Manipulating Data and Generate Reports After a DBMS has been installed, employees, managers, and consumers can use it to review reports and obtain important information. Some databases use Query-by-Example which is a visual approach to developing database queries or requests. Alternatively, SQL can be used to query a database  For example: SELECT * FROM EMPLOYEE WHERE JOB_CLASSIFICATION = “C2”. This will output all employees who have a job classification of ‘C2’ The '* ‘ tells the DBMS to include all columns from the EMPLOYEE table in the results In general, the commands that are used to manipulate the database are part of the data manipulation language (DML) which SQL is an example.  Database Output After a database has been set up and loaded with data, it can produce any desired reports, documents These outputs usually appear in screen displays or hard- copy printouts The output-control features of a database program allow you to select the records and fields to appear in reports You can also make calculations specifically for the report by manipulating database fields Formatting controls and organization options (such as report headings) help you to customize reports and create flexible, convenient, and powerful information-handling tools Questions