Database Systems - Lecture 7

Database SystemsLecture 7Summary of Lecture 6In the previous lecture we have discussedDataData Types, Representation, storageNumber Systems, Decimal, BinaryCodes to represent dataAmerican Standard Code for Information Interchange (ASCII), Extended Binary Coded Decimal Interchange Code (EBCDIC), UnicodeSummary of Lecture 6InformationHow Data is converted into InformationKnowledgeData SourcesDirect, IndirectData Quality MattersGIGOMS Word Practical SessionToday’s TopicsWhat is database system?Database Management System.Traditional File Processing SystemsDatabase Systems Types, ExamplesHow to develop a Database SystemHow data is organized and stored in Database SystemsSummary of TodayFamous DBMS Software.What You Need to do to develop yours own database systemsIn the Beginning, There Were File-Processing SystemsFile a collection of records containing logically related data arranged in fieldsFile Based SystemsFile-based system are thecollection of application programs that manage data specific to each programFile Processing Systems used to keep separate programs having 0 sharing or no logical relevance's. Files specific to file Base System Program Can be opened hereDifferent Files on a DesktopIt is difficult to search for data here!Some Limitation of file based systemsSeparation and isolation of dataData relevant to some business may store in different files. Which may be difficult to be related or searched.Data is duplicated.As same business data is being stored in different files, so there are lot of chances of duplication.Each application requires entering the same data again so:What is Data Duplication?Removing duplicatesData With Lot of DuplicatesExample: A Person Name and address may be stored in several different files.Some Limitations of File SystemsLack of Security.Somebody deletes the entire file/contents of the file. Data will be lost.Multi-User SupportA file based systems like MS Excel, Notepad etc. do not support multi-user access.Database SystemsSolution is to use Database Systems.A database is defined as an organized and logically related data, which is stored in well defined data structure.Beside this database also contains a metadata, which is data about data.eureka!Database Management Systems DBMS (database management system):Collection of programs that manages database structure and controls access to dataPossible to share data among multiple applications or usersMakes data management more efficient and effectiveSome popular DBMS SoftwaresWhy Database System?A database is self-describing.It contains meta data. Which describes structure and format related information of data. All such information is stored in data-dictionary within DBMS.Advantages of using DBMSData redundancy is controlled. It incorporates data consistency. This was a big disadvantage of file system.Data is stored in organized, logically related and well defined structure format.Thus no duplicates are allowed in databases.Can you find redundancy?Data Generated DataUsing database systems, business managers can see reports, perform calculations and other tasks.Thus more precise and summarized data can be viewed.Managers can view it from their own defined perspectives.Concurrent AccessDBMS provides concurrent access to the database.Many users can simultaneously access the database server.SecurityDBMS provides mechanism for proper security.it has Authorization and Authentication mechanism.Some other advantagesData can be easily recovered if lost.Flexibility for multiple needs.Rules and checks. How data is logically related in database?Relational database system concepts are used to organize and relate data and its types.Oracle, Microsoft SQL Server and others mostly follows relational database systems.Data is stored in a table.A Microsoft Access TableTable NameField NameRecord or a ROWTable DataRelational Model RepresentationEmployeeProjectWorks onEntity Relationship Diagram is used to conceptually represent Relational database.Database Systems in workNational Database and Registration Authority (NADRA)Hospital database systemsCOMSATS Student Information Systems (COMSIS).National Disaster Management SystemPayroll systemsAirline reservation systemsLogistic database systemNational Database and Registration AuthorityPakistan’s national largest database system.Eliminated bogus and duplicated cards which existed in previous.Database is used for effective law enforcement and administrative control.It is also being used for planning and functioning purpose.A Data Entry Form Example of National DatabaseHospital Database SystemContains patients related information. Patient Records from point of entry to point of exit as well as subsequent visits etc. Patient records are very important to any hospital, for follow up procedures and research purposes hence there is the need to manage patient data very accurately.COMSATS Student Information SystemCOMSISA complete database system for managingStudentFacultyHuman Resource SystemAdministrationExaminationRelated data.Provides on time solution support to various departments.COMSISFaculty can enter attendance and marks.Results are generated automatically.Students can view the result from their login.Daily progress can be monitored.National Disaster Management SystemUsed for risk analysis and support purpose.Planning for food, life saving items for needed areas.Can be linked with National Database.Can help decision makers about disaster risks and scale of help needed.International System for Disaster ManagementPayroll SystemIn any organization, payroll is the net result of its all financial matters and employee salaries.Taxable income and other matters are subject to law and regulations.Employees get automated salary slips each month, which includes detail of salary additions and deductions.Airline reservation systemIt mainly contains information aboutFlight CustomerReservations Purchase Credit cards etc.Now a customer can plan a journey by sitting at home using online airline reservation system.It keeps airline schedules, fare tariffs, passenger reservations and ticket records. PIA Seats Availability database searchLogistic DatabaseLogistic is the flow of resources concerned mainly with goods, items etc. It is between the point of origin to the point of delivery.Many commercial companies are doing logistic support. FedEx is the best example.It involves transportation, inventory, warehousing, material handling, and packaging, and often security. FedEx Pakistan online database trackingDatabase Design TermsTableData TypesPrimary KeyForeign KeyChecksQueriesFormsReportsTableAs discussed earlier, all the data in the database is stored in tables.Table contains vertical columns which are identified by their name and horizontal rows.Each row contains single instance of a record.Data TypesEach column or field has a data type.Data type defines the type of data to be stored. For example Numeric data, alpha numeric data, floating point data etc.Primary KeyIt is used in a table to uniquely identify a record. For example Student Registration Number in Student table.Using Registration Number, we can find a student. Primary Key can not by left NULL or it can not be repeated.Primary Key Setting in AccessIt can be set by right clicking on the Field Name.Foreign KeyRelational database makes relations using Primary and Foreign keys.When the primary key of any table is used in any other table as a reference, it is identified as a foreign key.Primary and foreign keys together make relational database design possible.Primary Key and Foreign Key Relations in MS AccessPrimary Key of Course TablePrimary Key of Student Table“StudentID” being used as a Foreign Key here.Checks or ConstraintsChecks in database systems enforce data integrity.It is the valid value to be stored in any field.They are necessary for correct and legal values to be stored.Examples are >10 means less then 10 value can not be stored here.“Is Not Null” check means that the particular field can not be left blank.ChecksQueryQueries are used to create table, modify table design and manipulate or search records stored in a database systems.Standard is SQL. SQL stands for “Structured Query Language”SQL is used in all relational database systems.It has easy to understand syntax.In MS Access Query can be created using a “Query Wizard”FormsAll data entry operations in database are done through FORMS.Forms are the interfaces through which users interact with the database.Users are given user friendly environment to enter data.New Student Data Entry FormReportsQuery can be used to develop a report.Report can present data in well furnished and comprehensive format.Database users view reports to get information.For example Medical Lab report related to test results.Report ExampleHow databases are developed?Software development life cycle model can be followed. This includes,1) Feasibility study 2) System analysis 3) System design 4) Coding 5) Testing and Implementation 6) Maintenance Feasibility studyIdentifies the scope of the current system, problems and unexploited opportunities in the current system.Then discusses the major objectives for the new system, and the various methods to gather data and determine how to use the methods. It also helps to estimate the costs of each possible solution, and develops estimates of the benefits and shortcomings of each solution.System analysisAssuming that a new system is to be developed, the next phase is analysis involved a detailed study of the current system, leading to specifications of a new system. During analysis, data are collected on the available files. Interviews, on-site observation and questionnaire are the tools used for system analysis.Analyzes the information needs of the end users.System designIt is based on the user requirements and the detailed analysis of a new system.The design proceeds in two stages : General design Detailed design System designGeneral design Show the users the view of the application so issues the System Flowcharts to develop. That describe how the data is to be processed by the computer will be developed.Detailed designcreates a plan for the design with the necessary specifications for the hardware, software, people and data resources.System designThere are several tools and techniques used for designing. Flowchart Data flow diagram (DFDs) Data dictionary Structured English CodingAfter designing the new system, the whole system is required to be converted into computer understanding language, which we refer as programs.Testing and ImplementationTestingBefore actually implementing the new system into operations, a test run of the system is done removing all the bugs.Using the test data following test run are carried out: Unit test System test Testing and ImplementationUnit test: When the programs have been coded and compiled and brought to working conditions, undesirable happening must be noted and debugged.System test: the test is done on actual data. it may be found that the outputs are not matching the expected out of the system. In such case, the errors are identified and are fixed and further tested for the expected output.Testing and ImplementationImplementationAfter having the user acceptance of the new system developed, the implementation phase begins.All the programs of the system are loaded onto the user's computer. After loading the system, training of the users starts.MaintenanceMaintenance is necessary to eliminate errors in the system during its working life and to tune the system to any variations in its working environment.Also means the review of the system from time to time. Summary of todayWe have learnt,Database importanceFile System Vs Database SystemsDatabase Management SystemsDifferent database systems example in practice.Essential concepts of database systems.Fields, Records, Primary Key, Foreign Key etc.How database systems are developed.