Database Management INFS 329 Slides PDF

INFS 329: Database Management Instructor: Mr. Commodore, [email protected]  Lecture Outline/Overview The key topics to be covered in this session are: 01 02 03 04 Definition of Definition of Applicat...

INFS 329: Database Management Instructor: Mr. Commodore, [email protected]  Lecture Outline/Overview The key topics to be covered in this session are: 01 02 03 04 Definition of Definition of Applications Limitations of Database Database and Benefits DBMS. terminologies Terminologies. of DBMS. LNK Commodore Tuesday, 24 January 2023 Slide 2 Learning Outcomes By the end of this lecture, students should be able to: ✓ Demonstrate a clear understanding of the database concepts. ✓ Distinguish a “database management system” from a “database”. ✓ Provide a historical account, in terms of the developmental landmarks to the present-day database systems. ✓ Explain how organisations and individuals are benefiting from database management systems; and the challenges associated thereof. LNK Commodore Tuesday, 24 January 2023 Slide 3  Topic 1 Database Terminologies Understanding basic terms in database management LNK Commodore Tuesday, 24 January 2023 Slide 5  Data, Information & Null What is Data? Raw facts/ unprocessed information. Data are the values that are stored in the database. What is metadata? Information is data that has been processed in a way that makes it meaningful. It can be shown as the result of a query, either displayed on-screen, or printed on a report. Null is a value that is either missing or unknown. A null value represents neither zero nor blank, as they are actual values and can be meaningful in certain circumstances. A drawback to null values is that they cannot be evaluated by mathematical expressions LNK Commodore Tuesday, 24 January 2023 Slide 6 Metadata “Data about data” ▪ Description of fields (e.g. File type) ▪ Display and format instructions ▪ Structure of files and tables ▪ Security and access rules ▪ Triggers and operational rules LNK Commodore Tuesday, 24 January 2023 Slide 7 What is a Database? An organized collection of related data. Coherent collection of data with inherent meaning, Random assortment of data is not a database About an aspect of the world Changes in the world are reflected in the database Fit to use for its intended purpose Somebody is going to use the database LNK Commodore Tuesday, 24 January 2023 Slide 8 Examples Phonebook Class Register A List of Registered Students for a course Look around you (beyond the class room) and share with the class some examples of a database. LNK Commodore Tuesday, 24 January 2023 Slide 9 Databases are Structured What are Structured Data? Categorised Data. Basic Element for organising data are tables. LNK Commodore Tuesday, 24 January 2023 Slide 10 An Example of a Database STUDENT: Name StudentNumber Class Major Smith 17 1 CSC Brown 8 2 ECE Green 24 2 CSC White 37 1 CSC GRADE_RPRT: StudentNo Course Grade 17 CSC742 A- 17 CSC316 B 24 CSC742 C- 8 CSC742 B+ LNK Commodore Tuesday, 24 January 2023 Slide 11 What is a File? A collection of records or documents dealing with one organization, person, area or subject. LNK Commodore Tuesday, 24 January 2023 Slide 12 Query What does it mean to query a database? Example query: Salesperson='Mary' AND Price>100 Give me your own examples from a database of items sold in a grocery shop. LNK Commodore Tuesday, 24 January 2023 Slide 13 More Terminologies… Entity --> What is this table about? Attribute (Field) --> What items of information are necessary to keep concerning this entity? ID, name, department, year, advisor. Property of an entity. Record (Tuple) --> A set of values for each attribute for one item LNK Commodore Tuesday, 24 January 2023 Slide 14 More Terminologies… Key --> The attribute used to define a required item. Types of keys: Primary Key: Key used to uniquely identify a record. Foreign Key: A field in a particular table which is the primary key of another table. Relationship --> Definitions linking two or more tables. An association among entities (entity records). LNK Commodore Tuesday, 24 January 2023 Slide 15 Summary of some salient DB terminologies (Shahid, 2018) LNK Commodore Tuesday, 24 January 2023 Slide 16  DB terminologies: Query LNK Commodore (eTutorials.org, 2008) Tuesday, 24 January 2023 Slide 17 What is Database Management Systems? Database Management This is about organising data in a way that will optimise data recording, updating, modification, securing etc. Database Management Systems Comprises programs to store, retrieve, and otherwise manage a computerized database and to provide interfaces to application programs and to non-programming users. Managing large amount of data. LNK Commodore Tuesday, 24 January 2023 Slide 18  DBMS Databas Databas e1 e2 Tables File 1 File 2 File 1 Records Fields/ attribute s LNK Commodore Tuesday, 24 January 2023 Slide 19  Database Management System Users / Programmers DATABASE Application Programs / Queries SYSTEM DBMS Processing Queries / Programs SOFTWARE Accessing Stored Data Stored Database Stored Definition Database (Meta-Data) LNK Commodore Tuesday, 24 January 2023 Slide 20 Schematic Layout of the Relationship between a Database Management Software (DBMS), the Database, and the Users Source: https://vivadifferences.com/wp-content/uploads/2019/10/DBMS.png LNK Commodore Tuesday, 24 January 2023 Slide 22  Schematic Layout of DBMS Image Source: https://www.learncomputerscienceonline.com/wp-content/uploads/2019/08/What-Is-Database-Management-System.jpg LNK Commodore Tuesday, 24 January 2023 Slide 23  Schematic Layout of DBMS Image Source: https://i.pinimg.com/originals/6e/42/24/6e4224c4f74475dd9cdfab357d34be6f.png LNK Commodore Tuesday, 24 January 2023 Slide 24 Topic Why Database (DB) & DBMS? 2 Areas of DB application Functions of a DBMS Benefits of DBM| The Down Side of DBs Course Instructor: Mr. Commodore Tuesday, 24 January 2023 Slide 25 Database Application Banking: For customer, information, account, and loans, and transaction. Airline: For reservation and schedule information. Airline were among the first to use database in a geographically distributed manner terminals situated around the world accessed the central database system through phone lines and other data network. Universities: For students' information, course registration, and grades. Credits card transaction: For purchases on credits cards and generation of monthly statements. Sales: For customer, product, and purchase information. Human resources: For information about employees, salaries, payroll taxes and benefits, and for generation of paychecks. LNK Commodore Tuesday, 24 January 2023 Slide 26 Functions of a DBMS Data Storage and Retrieval Managing Metadata Limiting and Controlling Redundant Data in Multiple Systems Supporting Simultaneous Data Sharing Providing Transaction Atomicity Providing Backup and Recovery Services Providing Authorization and Security Services Enforcing (Business) Rules More light will be shed on these functions as we move on to discuss the advantages of DBMS LNK Commodore Tuesday, 24 January 2023 Slide 27  TOPIC Advantages & Disadvantages of DBMS 3 LNK Commodore Tuesday, 24 January 2023 Slide 38  Advantages of DBMS Minimize Data Redundancy – In File Processing System, duplicate data is created in many places because all the programs have their own files. This creates data redundancy which in turns wastes labor and space. In Database Management System, all the files are integrated in a single database. The whole data is stored only once at a single place so there is no chance of duplicate data. Sharing Of Data – In DBMS, Data can be shared in between authorized user of database. All the users have their own right to access the database up to a level. Database Administration has complete access of database. He can assign users to access the database. Others users are also authorized to access database and also they can share data between them. Many users have same authority to access the database. LNK Commodore Tuesday, 24 January 2023 39  Advantages of DBMS Data Consistency – DBMS controls data redundancy which in turn controls data consistency. Data consistency means if you want to update data in any files then all the files should not be updated again. As in DBMS, data is stored in a single database so data becomes more consistent in comparison to file processing system. Also updated values are available to all the users immediately. Data Integrity – Data integrity means unification of so many files into a single file. In DBMS data is stored in different tables. A database contains different tables that are linked to each other. Many users feed entries in these tables so it is important to maintain data items and association between data items. DBMS allows data integrity that makes it easy to decrease data duplicity Data integration reduces redundancy as well as data inconsistency. LNK Commodore DR. EBENEZER ANKRAH Tuesday, 24 January 2023 40  Advantages of DBMS Search Capability – Users of database may require to fetch data from the database. There are numerous queries users may ask about the data. Search speed of the database must be fast to produce quick results. If users execute any query then it is required that he get fastest results from the database. It is an objective of database to maintain flexible search capability. Security – Data security means protecting your precious data from unauthorized access. Data in database should be kept secure and safe to unauthorized modifications. Only authorized users should have the grant to access the database. There is a username set for all the users who access the database with password so that no other guy can access these information. DBMS always keep database tamperproof, secure and theft free. LNK Commodore DR. EBENEZER Tuesday, 24 January 2023 41 ANKRAH  Advantages of DBMS Privacy – Privacy means up to what extent a user can access the data. It is predetermined by the DBA that who will access the data and up to what level he will be able to access it. Let say when you make a Facebook page then you have the power to give rights to other users that who will be the promoter, editor and admin. Simplicity – Simplicity means to represent the overall logical view of data in a simple and clear manner. DBMS is very simple for its users who use it. All the operations like insert, delete, create and update are very easy to implement. LNK Commodore Tuesday, 24 January 2023DR. EBENEZER 42 ANKRAH  Advantages of DBMS Backup and Recovery – Data loss is a very big problem for all the organizations. In traditional file processing system, a user needs to backup the database after a regular interval of time that wastes lots of time and resources. If the volume of data is large then this process may take a very long time. – DBMS solves this problem of taking backup again and again because it allows automatic backup and recovery of database. For examples, if a system fails in the middle of any process then DBMS stores the values of that state in which database were before query execution. LNK Commodore DR. EBENEZER ANKRAH Tuesday, 24 January 2023 43  Advantages of DBMS Integrity Constraints – Constraints are used to store accurate data because there are many users who feed data in database. Data stored in database should always be correct and accurate. DBMS provides the capability to enforce these – constraints on database. For example, the maximum marks obtained by the students can never be more than 100. Also account balance of Banks like Axis should not be less than 2500 otherwise you will be penalized. Data Atomicity – Any complete transaction in database is called atomic unit. It is the duty of DBMS to store a complete transaction in database. If any transaction is partially completed then it rolls back to them. – For example, in railway reservation system, if user has completed the process of ticket reservation then his record will be stored and amount of money will be deducted from his account otherwise no amount will be deducted and if deducted it will be given back. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 44  Advantages of DBMS Development of new applications – If a new application is required and data is available for creating the application then it is very easy to develop new application. No time will be consumed in creating stored data again and again. Concurrency Control – If two users are accessing data simultaneously and they both want to update values of same record then it may create concurrency. DBMS has the power to control concurrency so that no transactions are lost. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 45  Advantages of DBMS Data Migration – Data migration means adjusting storage of data according to its popularity. In a database, there is some kind of data that is accessed frequently and at the same time some data is accessed occasionally. So it is required to store frequently accessed data in a manner that it can be accessed quickly. Tunability – Tuning means adjusting something to get better performance. Same in the case of DBMS, as it provides tunability to improve performance. DBA adjust database to get effective results. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 46  Advantages of DBMS Maintaining Cost is lower – DBMS systems are costly but after purchasing them their maintenance cost is very less. It can be maintained by few programmers that is not costly for an enterprise. Standards can be enforced – As DBMS have central control of database so a DBA can ensure that all the applications follow some standards such as format of data, document standards etc. These standards help in data migrations or in interchanging the data. Tuesday, 24 DR. EBENEZER ANKRAH LNK Commodore 48 January 2023  Advantages of DBMS Very Less Chances of Data Loss – As there is lot of security constraint made on database so chances of data loss are minimum. One can store their precious data or many years in DBMS without loss of any information. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 49  Disadvantages of DBMS LNK Commodore Tuesday, 24 January 2023 SLIDE 50  Disadvantages of DBMS Complexity – The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end- users must understand this functionality to take full advantage of it. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization. Size – The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 51  Disadvantages of DBMS Performance – Typically, a File Based system is written for a specific application, such as invoicing. As result, performance is generally very good. However, the DBMS is written to be more general, to cater for many applications rather than just one. The effect is that some applications may not run as fast as they used to. Higher impact of a failure – The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the ~vailabi1ity of the DBMS, the failure of any component can bring operations to a halt. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 52  Disadvantages of DBMS Cost of DBMS – The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost. Additional Hardware costs – The disk storage requirements for the DBMS and the database may necessitate the purchase of additional storage space. Furthermore, to achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 53  Disadvantages of DBMS Cost of Conversion – In some situations, the cost of the DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. – This cost also includes the cost of training staff to use these new systems and possibly the employment of specialist staff to help with conversion and running of the system. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology. DR. EBENEZER ANKRAH LNK Commodore Tuesday, 24 January 2023 54  Lecture Summary LNK Commodore Tuesday, 24 January 2023 Slide 55 Summary of today’s class LNK Commodore Tuesday, 24 January 2023 SLIDE 56 Works Consulted Hoffer, J. A., Prescott, M. B., & Topi, H. (2009). Modern Database Management. Pearson Prentice Hall. Helman, P. (2000). The Science of Database Management. IRWIN. Boston, Massachusetts. R. R. Donnelly and Sons Company. Pratt, A. & Adamski, T. (2009). Introduction to Data Management. Chapter 1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. Boston, Massachusetts. WCB: McGraw-Hill. LNK Commodore Tuesday, 24 January 2023 Slide 57 LNK Commodore Tuesday, 24 January 2023 Slide 58 INFS 329: Database Management Dr. Ebenezer Ankrah ([email protected]) Mr Lenin Commodore ([email protected]) 2022/2023 Academic Year Lecture Outline/Overview The key topics to be covered in this session are: 01 02 03 04 Some key Benefits of Limitations Lecture features of DBMS of DBMS. Summary DBMSs Wednesday, 08 February LNK Commodore Slide 2 2023 Learning Outcomes By the end of this lecture, students should be able to: ✓ Explain how organisations and individuals are benefiting from database management systems; and the challenges associated thereof. Wednesday, 08 February LNK Commodore 2023 Slide 3 TOPIC Some key features of DBMSs 1 LNK Commodore 2022/2023 Wednesday, 08 February 2023 Academic Year Slide 4 Some Features / Functionalities of a DBMS (Cont’d) Integrity Constraints Constraints are used to store accurate data because there are many users who feed data in database. Data stored in database should always be correct and accurate. DBMS provides the capability to enforce these constraints on database. For example, the maximum marks obtained by the students can never be more than 100. Also account balance of Banks like Axis should not be less than 2500 otherwise you will be penalized. Concurrency Control If two users are accessing data simultaneously and they both want to update values of same record, then it may create concurrency. DBMS has the power to control concurrency so that no transactions are lost. Wednesday, 08 February LNK Commodore Slide 5 2023 Some Features / Functionalities of a DBMS Data Migration Data migration means adjusting storage of data according to its popularity. In a database, there is some kind of data that is accessed frequently and at the same time some data is accessed occasionally. So, it is required to store frequently accessed data in a manner that it can be accessed quickly. Data Atomicity Any complete transaction in database is called atomic unit. It is the duty of DBMS to store a complete transaction in database. If any transaction is partially completed, then it rolls back to them. “All or nothing”. For example, in railway reservation system, if user has completed the process of ticket reservation, then his record will be stored, and amount of money will be deducted from his account otherwise no amount will be deducted and if deducted it will be given back. Wednesday, 08 February LNK Commodore Slide 6 2023 Some Features / Functionalities of a DBMS (Cont’d) Data Atomicity Wednesday, 08 February LNK Commodore Slide 7 2023 Some Features / Functionalities of a DBMS (Cont’d) Wednesday, 08 February LNK Commodore Slide 10 2023  TOPIC Advantages & Disadvantages of DBMS 3 LNK Commodore Wednesday, 08 February 2023 2022/2023 Academic Year Slide 11 Advantages of DBMS Minimize Data Redundancy – In File Processing System, duplicate data is created in many places because all the programs have their own files. This creates data redundancy which in turns wastes labor and space. In Database Management System, all the files are integrated in a single database. The whole data is stored only once at a single place so there is no chance of duplicate data. Sharing Of Data – In DBMS, Data can be shared in between authorized user of database. All the users have their own right to access the database up to a level. Database Administration has complete access of database. He can assign users to access the database. Others users are also authorized to access database and also they can share data between them. Many users have same authority to access the database. Wednesday, 08 February LNK Commodore 12 2023  Advantages of DBMS Data Consistency – DBMS controls data redundancy which in turn controls data consistency. Data consistency means if you want to update data in any files then all the files should not be updated again. As in DBMS, data is stored in a single database so data becomes more consistent in comparison to file processing system. Also updated values are available to all the users immediately. Data Integrity – Data integrity means unification of so many files into a single file. In DBMS data is stored in different tables. A database contains different tables that are linked to each other. Many users feed entries in these tables so it is important to maintain data items and association between data items. DBMS allows data integrity that makes it easy to decrease data duplicity Data integration reduces redundancy as well as data inconsistency. Wednesday, 08 February DR. 13 EBENEZER ANKRAH LNK Commodore 2023  Advantages of DBMS Search Capability – Users of database may require to fetch data from the database. There are numerous queries users may ask about the data. Search speed of the database must be fast to produce quick results. If users execute any query then it is required that he get fastest results from the database. It is an objective of database to maintain flexible search capability. Security – Data security means protecting your precious data from unauthorized access. Data in database should be kept secure and safe to unauthorized modifications. Only authorized users should have the grant to access the database. There is a username set for all the users who access the database with password so that no other guy can access these information. DBMS always keep database tamperproof, secure and theft free. Wednesday, 08 FebruaryDR. EBENEZER ANKRAH LNK Commodore 14 2023  Advantages of DBMS Privacy (This is a part of security) – Privacy means up to what extent a user can access the data. It is predetermined by the DBA that who will access the data and up to what level he will be able to access it. Let say when you make a Facebook page then you have the power to give rights to other users that who will be the promoter, editor and admin. Simplicity – Simplicity means to represent the overall logical view of data in a simple and clear manner. DBMS is very simple for its users who use it. All the operations like insert, delete, create and update are very easy to implement. Wednesday, 08 February LNK Commodore 2023 DR. EBENEZER 15 ANKRAH  Advantages of DBMS Backup and Recovery – Data loss is a very big problem for all the organizations. In traditional file processing system, a user needs to backup the database after a regular interval of time that wastes lots of time and resources. If the volume of data is large then this process may take a very long time. – DBMS solves this problem of taking backup again and again because it allows automatic backup and recovery of database. For examples, if a system fails in the middle of any process then DBMS stores the values of that state in which database were before query execution. Wednesday, 08 February LNK Commodore DR. EBENEZER ANKRAH 2023 16 Advantages of DBMS Development of new applications If a new application is required and data is available for creating the application, then it is very easy to develop new applications. No time will be consumed in creating stored data again. Tunability Tuning means adjusting something to get better performance. Same in the case of DBMS, as it provides tunability to improve performance. Database administrators (DBAs) adjust the database to get effective results. Wednesday, 08 February LNK Commodore Slide 17 2023  Advantages of DBMS Maintaining Cost is lower – DBMS systems are costly but after purchasing them their maintenance cost is very less. It can be maintained by few programmers that is not costly for an enterprise. Standards can be enforced – As DBMS have central control of database so a DBA can ensure that all the applications follow some standards such as format of data, document standards etc. These standards help in data migrations or in interchanging the data. DR. LNKEBENEZER ANKRAH Commodore Wednesday, 08 February 2023 18  Disadvantages of DBMS LNK Commodore Wednesday, 08 February 2023 2022/2023 Academic Year SLIDE 19  Disadvantages of DBMS Complexity – The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end- users must understand this functionality to take full advantage of it. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization. Size – The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently. DR. LNKEBENEZER ANKRAH Commodore Wednesday, 08 February 2023 20  Disadvantages of DBMS Performance – Typically, a File Based system is written for a specific application, such as invoicing. As result, performance is generally very good. However, the DBMS is written to be more general, to cater for many applications rather than just one. The effect is that some applications may not run as fast as they used to. Higher impact of a failure – The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the ~vailabi1ity of the DBMS, the failure of any component can bring operations to a halt. DR. LNKEBENEZER ANKRAH Commodore Wednesday, 08 February 2023 21  Disadvantages of DBMS Cost of DBMS – The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost. Additional Hardware costs – The disk storage requirements for the DBMS and the database may necessitate the purchase of additional storage space. Furthermore, to achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure. DR. LNKEBENEZER ANKRAH Commodore Wednesday, 08 February 2023 22  Disadvantages of DBMS Cost of Conversion – In some situations, the cost of the DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. – This cost also includes the cost of training staff to use these new systems and possibly the employment of specialist staff to help with conversion and running of the system. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology. DR. LNKEBENEZER ANKRAH Commodore Wednesday, 08 February 2023 23 Activity 2.1 Discuss the challenges and benefits of database management software. Identify any three (3) setbacks associated with DBMS, and discuss how these challenges can be curbed. Wednesday, 08 February LNK Commodore Slide 24 2023 Works Consulted Hoffer, J. A., Prescott, M. B., & Topi, H. (2009). Modern Database Management. Pearson Prentice Hall. Helman, P. (2000). The Science of Database Management. IRWIN. Boston, Massachusetts. R. R. Donnelly and Sons Company. Pratt, A. & Adamski, T. (2009). Introduction to Data Management. Chapter 1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. Boston, Massachusetts. WCB: McGraw-Hill. Wednesday, 08 February LNK Commodore Slide 25 2023  Wednesday, 08 February LNK Commodore Slide 26 2023  INFS 329: Database Management Dr. Ebenezer Ankrah ([email protected]) Mr Lenin Commodore ([email protected]) 2022/2023 Academic Year Mr. Commodore, [email protected] Department of Information Studies Lecture Overview Slide 2 Lecture Outline The key topics to be covered in this session are: 01 02 03 04 Physical File File Access File Systems Storage Organisation & File Media Structures Slide 3 Physical Storage Media Topic 1 2022/2023 Aca. Yr. SLIDE 5 Physical Storage Media Slide 6 Physical Storage Media – Cont’d. Slide 7 Physical Storage Media – Cont’d. Slide 8 Physical Storage Media – Cont’d. Slide 9 Physical Storage Media – Cont’d. Solid State Drive (SSD) The solid-state contrasts with a hard disk drive (HDD), which consists of components with hollow and moving states. It also means that when an SSD reads or writes information or data, it does not require moving parts. SSD is flash technology-based storage as it does not require power. Hence, it is also called as nonvolatile flash memory. Slide 10 File Organisation Topic 2 2022/2023 Aca. Yr. SLIDE 11 File Organisation A file is a collection of data, usually stored on disk. As a logical entity, a file enables you to divide your data into meaningful groups, for example, you can use one file to hold all of a company's product information and another to hold all of its personnel information. As a physical entity, a file should be considered in terms of its organization. The term "file organization" refers to the way in which data is stored in a file and, consequently, the method(s) by which it can be accessed. Slide 12 File Organisation – Cont’d. File organization is the methodology which is applied to structured computer files. Files contain computer records which can be documents or information which is stored in a certain way for later retrieval. File organization refers to the way records are physically arranged on a storage device (Intel Corporation, 2011). File Organization defines how file records are mapped onto disk blocks. Slide 13 Types of File Organization Slide 14 Heap (unordered) File Organization Slide 15 Heap File Organization (Cont’d.) Slide 16 Heap File Organization (Cont’d.) Slide 17 Hash File Organization Slide 18 Hash File Organization (Cont’d) Records are organised using a technique called hashing. A hash function is a function that takes a set of inputs of any arbitrary size and fits them into a table or other data structure that contains fixed-size elements.... The table or data structure generated is usually called a hash table. There are several algorithms for hashing. Slide 19 Hash File Organization (Cont’d) Slide 20 Indexes Sequential Access Method (ISAM) Slide 21 ISAM (Cont’d.) (Dhanokar, 2020) Slide 22 B+ Tree Slide 23 B+ Tree Slide 24 B+ Tree (Cont’d.) Each (internal/leaf) node is stored as one disk block. http://www.mathcs.emory.edu Slide 25 Clustered Tables File Organization Slide 26 File Access & File Structures Topics 3 2022/2023 Aca. Yr. SLIDE 27 File Access & File Structures Methods of file access: Sequential Direct Stream File structures: Formatted Unformatted Binary Sequential-access and direct-access files can have any of the three file structures. Stream-access files can have a file structure of formatted or unformatted. (Intel Corporation, 2011) Slide 28 Sequential-Access Files Data in sequential files must be accessed in order, one record after the other (unless you change your position in the file with the REWIND or BACKSPACE statements). Some methods of I/O are possible only with sequential files, including non-advancing I/O, list-directed I/O, and name-list I/O. Internal files also must be sequential files. You must use sequential access for files associated with sequential devices. A sequential device is a physical storage device that does not allow explicit motion (other than reading or writing). (Intel Corporation, The 2011) keyboard, screen, and printer are all sequential devices. Slide 29 Direct-Access Files Data in direct-access files can be read or written to in any order. Records are numbered sequentially, starting with record number 1. All records have the length specified by the RECL option in the OPEN statement. Data in direct files is accessed by specifying the record you want within the file. (Intel Corporation, 2011) If you need random access I/O, use direct-access files. A common example of a random-access application is a database. Slide 30 Stream-Access Files Stream access I/O is a method of accessing a file without reference to a record structure. With stream access, a file is seen as a continuous sequence of bytes and is addressed by a positive integer starting from 1. (Intel Corporation, 2011) Slide 31 File Systems Topic 4 2022/2023 Aca. Yr. SLIDE 32 File Systems A file system is a method of organizing files on physical media, such as hard disks, CD's, and flash drives. File Windows macOS Ubuntu PlayStation Xbox System 7/8/10 (10.6.5 & Linux 4 360/On later) e NTFS Yes Read Yes No No/Ye Only s FAT32 Yes Yes Yes Yes Yes/Ye s exFAT Yes Yes Yes Yes No/Ye s Slide 33 Activity 1.1 1. Read on the performances and capabilities of the various file systems at https://kb.wisc.edu/helpdesk/page.php?id=11300. Slide 36  References Hoffer, J. A., Prescott, M. B., & Topi, H. (2011). Modern Database Management. Pearson Prentice Hall. Helman, P. (2000). The Science of Database Management. IRWIN. Boston, Massachusetts. R. R. Donnelly and Sons Company. Intel Corporation. (2011a). File Access & File Structures. Fortran I/O. Available at: https://scc.ustc.edu.cn/zlsc/sugon/intel/compiler_f/main_for/bldaps_f or/common/bldaps_fileacc.htm Intel Corporation. (2011b). File Organization. Fortran I/O. Available at: https://scc.ustc.edu.cn/zlsc/sugon/intel/compiler_f/main_for/bldaps_f or/common/bldaps_fileorg.htm Pratt, A. & Adamski, T. (2009). Introduction to Data Management. Chapter 1. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. Boston, Massachusetts. WCB: McGraw-Hill. Slide 39 Slide 40  INFS 329: Database Management (1st Semester, 2022/2023) Lectures 04: THE DATABASE DEVELOPMENT PROCESS Mr. Commodore, [email protected] Lecture Overview A core aspect of software engineering is the subdivision of the development process into a series of phases, or steps, each of which focuses on one aspect of the development. The collection of these steps is sometimes referred to as a development life cycle. This session provides direction to students on how to identify and understand each of the steps in the database development process. Slide 2 Introduction This Session ultimately seeks to address the question: “How do organizations develop databases?” We will look at some of the popular ways of developing databases and the tools used in the the development process. The approaches include the EDM, the SDLC, DBDLC, and Prototyping, among others. Slide 3 Lecture Outline The key topics to be covered in this session are: 01 02 03 04 05 Enterprise SDLC DBDLC Prototyping Tools in Data Approach Approach Database Modelling Design & Development Slide 4 Recommended Text Hoffer, J. A., Prescott, M. B., & Topi, H. (2011). Modern Database Management. Pearson Prentice Hall. Slide 5 The Enterprise Data Modelling Approach Topic 1 SLIDE 6 The Enterprise Data Modelling Approach How do organizations start developing a database? In many organizations, database development begins with Enterprise Data Modeling (EDM), which establishes the range and general contents of organizational databases. The purpose of EDM is to create an overall picture or explanation of organizational data, not the design for a particular database. A particular database provides the data for one or more information systems, whereas an enterprise data model, which may encompass many databases, describes the scope of data maintained by the organization. (Hoffer, Prescott, & Topi, 2011). Slide 7 EDM Approach (Cont’d). In enterprise data modelling, you review current systems, analyze the nature of the business areas to be supported, describe the data needed at a very high level of abstraction, and plan one or more database development projects. EDM projects often develop new databases to meet strategic organizational goals, such as improved customer support, better production and inventory management, or more accurate sales forecasting. (Hoffer, Prescott, & Topi, 2011). Slide 8 EDM Approach (Cont’d). Enterprise data modeling is a top-down approach to database development. However, many database projects arise, in a more bottom- up fashion, for example the prototyping approach and the SDLC approach. In such cases, projects are requested by information systems users, who need certain information to do their jobs, or from other information systems professionals, who see a need to improve data management in the organization. A typical bottom-up database development project usually focuses on the creation of one database. (Hoffer, Prescott, & Topi, 2011). Slide 9 Systems Development Life Cycle Approach Topic 2 SLIDE 10 SDLC to Database Development. SDLC is the traditional methodology used to develop, maintain, and replace information systems. A database and the associated information processing functions are developed together as part of a comprehensive information systems development project. The SDLC process appears to be circular and is intended to convey the iterative nature of systems development projects. The steps may overlap in time, they may be conducted in parallel, and it is possible to backtrack to previous steps when prior decisions need to be reconsidered. (Hoffer, Prescott, & Topi, 2011). Slide 11 SDLC Approach to Database Development (Cont’d.) The SDLC approach to database development outlines the database development activities typically included in each phase of the SDLC. Note that there is not always a one-to-one correspondence between SDLC phases and database development steps. For example, conceptual data modelling occurs in both the Planning and the Analysis phases. The stages in the SDLC are: Planning, Analysis, Design, Implementation & Maintenance. (Hoffer, Prescott, & Topi, 2011). Slide 12 SDLC & Database Development Planning Enterprise Modelling Conceptual Data Modelling Maintenance Analysis Database Conceptual Data Maintenance. Modelling Implementation Design Database Logical Database Design Implementation Physical Database Design Slide 13  PLANNING—ENTERPRISE MODELING The database development process begins with a review of the enterprise modeling components that were developed during the information systems planning process. During this step, analysts review current databases and information systems, analyze the nature of the business area that is the subject of the development project, and describe, in very general terms, the data needed for each information system under consideration for development. They determine what data are already available in existing databases and what new data will need to be added to support the proposed new project. Only selected projects move into the next phase based on the projected value of each project to the organization. (Hoffer, Prescott, & Topi, 2011). Slide 14 PLANNING—CONCEPTUAL DATA MODELING For an information systems project that is initiated, the overall data requirements of the proposed information system must be analyzed. During this phase, the analyst develops diagrams to represent/ model the data concepts. There is also documentation, to outline the Scope of data involved in the new database that is being developed (without consideration of what databases already exist). (Hoffer, Prescott, & Topi, 2011). Slide 15 ANALYSIS—CONCEPTUAL DATA MODELING During the Analysis phase of the SDLC, the analyst produces a detailed data model that identifies all the organizational data that must be managed for this information system. Every data attribute is defined, all categories of data are listed, every business relationship between data entities is represented, and every rule that dictates the integrity of the data is specified. The output of the conceptual modelling phase is a conceptual schema. The conceptual schema is a detailed, technology-independent specification of the overall structure of organizational data (Hoffer, Prescott, & Topi, 2011). Slide 16 DESIGN—LOGICAL DATABASE DESIGN Logical database design approaches database development in stages. 1. First, the conceptual schema must be transformed into a logical schema, which describes the data in terms of the data management technology that will be used to implement the database. 2. Second, each application in the information system is designed, including the program’s input and output formats, the analyst performs a detailed review of the transactions, reports, displays, and inquiries supported by the database. 3. The final step is to transform the combined and reconciled data specifications into basic, or atomic, elements following well-established rules for well-structured data specifications. Slide 17 DESIGN—PHYSICAL DATABASE DESIGN AND DEFINITION In physical database design and definition, an analyst decides on the organization of physical records, the choice of file organizations, the use of indexes, and so on. A physical schema is a set of specifications that describe how data from a logical schema are stored in a computer’s secondary memory by a specific database management system. There is one physical schema for each logical schema. Physical database design requires knowledge of the specific DBMS that will be used to implement the database. (Hoffer, Prescott, & Topi, 2011). Slide 18 IMPLEMENTATION—DATABASE IMPLEMENTATION In database implementation, a designer writes, tests, and installs the programs/scripts that access, create, or modify the database. The designer might do this using standard programming languages (e.g., Java, C#, or Visual Basic.NET), in special database processing languages (e.g., SQL). Also, during implementation, the designer will finalize all database documentation, train users, and put procedures into place for the ongoing support of the information system (and database) users. The last step is to load data from existing information sources (files and databases from legacy applications plus new data now needed). (Hoffer, Prescott, & Topi, 2011). Slide 19 MAINTENANCE—DATABASE MAINTENANCE The database evolves during database maintenance. In this step, the designer adds, deletes, or changes characteristics of the structure of a database in order to meet changing business conditions, to correct errors in database design, or to improve the processing speed of database applications. The designer might also need to rebuild a database if it becomes contaminated or destroyed due to a program or computer system malfunction. This is typically the longest step of database development, because it lasts throughout the life of the database and its associated applications. (Hoffer, Prescott, & Topi, 2011). Slide 20 Prototyping Topic 3 SLIDE 21 Prototyping Prototyping is an iterative process of systems development in which requirements are converted to a working system that is continually revised through close work between analysts and users. A prototype is like a model or a simulation of a real thing. In systems analysis a prototype is a model of the system (or subsystem) under analysis. A system can be anything from the food ordering system at a restaurant to the air traffic control system of a major airport. Prototypes of these systems can take many forms. The purpose of a prototype is to allow the users to use the prototype to identify the features of the system using the computer. (Hoffer, Prescott, & Topi, 2011). Slide 22 (Dobry, 2022) Prototyping (Cont’d) Building working model of a database system. It does not contain all the required features. Purpose – to identify features of a system that are inadequate; – to suggest improvements or even new features; – to clarify the users’ requirements; – to evaluate feasibility of a particular system design. Slide 24 Prototyping (Cont’d). Slide 25 Prototyping Methodology Slide 26 Prototyping Methodology (Cont’d). Slide 27 TOPIC Database Development Life Cycle (DBDLC) 4 Slide 28 DBDLC The traditional lifecycle models miss some of the requirements for database system development. These SDLCs are generally defined very broadly and are not specific for a particular type of application. Some experts emphasize that there should be an SDLC that is specific to database applications. Database applications do not have the same characteristics as other software applications and thus a specific database development life cycle (DBDLC) is needed. Slide 29 DBDLC (Cont’d). The DBDLC model satisfies [specific] properties [of database development] such as scope restriction, progressive enhancement, incremental planning and pre- defined structure. The database development life cycle (DDLC) is inherently associated with the software development life cycle (SDLC) of the information system (Singh, 2009). Slide 30  Database System Development Lifecycle Within the larger information system, the database, too, is subject to a life cycle. The Database Life Cycle (DBLC) contains six phases: 1. database initial study, 2. database design, 3. implementation and loading, 4. testing and evaluation, 5. operation, 6. maintenance and evaluation. (Coronel, Morrison, Rob, 2011) Slide 31 TOPIC Tools for Database Design & Development 5 CASE Tools Slide 33 Tools for Database Design & Development Computer-aided software engineering (CASE) tools CASE tools are automated tools used to design databases and application programs. A computer-aided software engineering (CASE) tool is a software package that provides support for the design and implementation of information systems. It can document a database design and provide invaluable help in maintaining the consistency of a design (Harrington, 2009). These tools help with creation of data models and in some cases can also help automatically generate the “code” needed to create the database. Slide 34 CASE Tools Slide 35 CASE Tools Slide 36 Using CASE Tools for Database Design (Harrington, 2016) Slide 37 Summary of today’s Lecture Tools in Enterprise SDLC Prototyping Database Data DBDLC Approach Approach Design & Modelling Development Slide 42 Slide 43  References Hoffer, J. A., Prescott, M. B., & Topi, H. (2011). Modern Database Management. Pearson Prentice Hall. Pratt, A. & Adamski, T. (2009). Introduction to Data Management. Harrington, J. L. (2009). Using CASE Tools for Database Design in Relational Database Design (Third Edition). – Chapter 3. Slide 44  INFS 329: Database Management DATA MODELS Mr. COMMODORE ([email protected]) Lecture Overview (1) In this lecture, we will examine data modeling. Data modeling is the first step in the database design journey, serving as a bridge between real-world objects and the database that resides in the computer. One of the most vexing problems of database design is that designers, programmers, and end users see data in different ways. Consequently, different views of the same data can lead to database designs that do not reflect an organization’s actual operation, failing to meet end-user needs and data efficiency requirements. Slide 2 Lecture Overview (2) To avoid such failures, database designers must obtain a precise description of the nature of the data and of the many uses of that data within the organization. Communication among database designers, programmers, and end users should be frequent and clear. Data modeling clarifies such communication by reducing the complexities of database design to more easily understood abstractions that define entities and the relations among them. Slide 3 Lesson Goal Participants in this lectures will learn what some of the basic data-modeling concepts are and how current data models developed from earlier models. Tracing the development of those database models will help you understand the database design and implementation issues Slide 4 Lecture Outline The key topics to be covered in this session are: 01 02 03 04 Data Data Models The The Evolution Modelling Importance of of Data Data Models Models Slide 5 Recommended Text Coronel, C., & Morris, S. (2017). Database systems: design, implementation, & management. (8th Ed). Nelson Education. Slide 6 Data Modelling & Data Models Topic 1 SLIDE 7 Data Model A data model is a relatively simple representation, usually graphical, of more complex real-world data structures. In general terms, a model is an abstraction of a more complex real-world object or event. A model’s main function is to help you understand the complexities of the real-world environment. Within the database environment, a data model represents data structures and their characteristics, relations, constraints, transformations, and other constructs with the purpose of supporting a specific problem domain. Data models are created through a process called data modelling. Slide 8 Data Modelling Database design focuses on how the database structure will be used to store and manage end-user data. Data modeling, the first step in designing a database, refers to the process of creating a specific data model for a determined problem domain. A problem domain is a clearly defined area within the real- world environment, with well defined scope and boundaries, that is to be systematically addressed. Slide 9 How Data Modelling Works (Recall Form Previous Lecture) Mr Commodore February 18, 2023  Levels of Data Modelling (Fernigrini, 2021) Slide 11 Data Model & Database Models The terms data model and database model are often used interchangeably. An implementation-ready data model should contain at least the following components: ―A description of the data structure that will store the end-user data. ―A set of enforceable rules to guarantee the integrity of the data. ―A data manipulation methodology to support the real-world data transformations. Slide 16 The Importance of Data Models Topic 2 SLIDE 17 The Importance of Data Models Communication tool Data models can facilitate interaction among the designer, the applications programmer, and the end user. A well-developed data model can even foster improved understanding of the organization for which the database design is developed. Bases for a database blue print A sound data environment requires an overall database blueprint based on an appropriate data model. Unifies data views in an organisation Data are viewed in different ways by different people. Slide 18 The Evolution of Data Models Topic 3 SLIDE 20 The Evolution of Data Models The evolution of DBMSs has always been driven by the search for new ways of modeling increasingly complex real- world data. In today’s class we will discuss some of the most commonly recognized data models. SLIDE 21 Evolution of the Major Data Models Slide 22 File Management Systems Provided facilities to extract data and share files but did not implement any way to connect records in one file to those in another. Relationships had to be implemented in application code. Database vs File Systems FILE SYSTEM Program 1 Meta-Data Program 2 Meta-Data Data Program 3 Meta-Data DATABASE Program 1 Meta- Program 2 Data Data Program 3 The hierarchical model The hierarchical model was developed in the 1960s to manage large amounts of data for complex manufacturing projects such as the Apollo rocket that landed on the moon in 1969. Its basic logical structure is represented by an upside- down tree. The hierarchical structure contains levels, or segments. A segment is the equivalent of a file system’s record type. Within the hierarchy, the top layer (the root) is perceived as the parent of the segment directly beneath it. Slide 25 A hierarchical structure Slide 26 Hierarchy SECTION STUDENT INSTRUCTOR COLLEGE COLLEGE Each file can have only one parent. To implement a second “parent” (COLLEGE) we have to implement a shadow copy. The Network Model The network model was created to represent complex data relationships more effectively than the hierarchical model, to improve database performance, and to impose a database standard. The lack of database standards was troublesome to programmers and application designers because it made database designs and applications less portable. Worse, the lack of even a standard set of database concepts impeded the search for better data models. Disorganization seldom fosters progress. Slide 29 The Network Model (Cont’d) To help establish database standards, the Conference on Data Systems Languages (CODASYL) created the Database Task Group (DBTG) in the late 1960s. The DBTG was charged to define standard specifications for an environment that would facilitate database creation and data manipulation. Slide 30 Network Model SECTION STUDENT INSTRUCTOR COLLEGE Each file can have several parents. Both SECTION and COLLEGE are “parent” files.. A network data model Slide 32 A network data model In this model, CUSTOMER, SALESREP, INVOICE, INV_LINE, PRODUCT, and PAYMENT represent record types. Note that INVOICE is “owned” by both SALESREP and CUSTOMER. Similarly, INV_LINE has two owners, PRODUCT and INVOICE. Furthermore, the network model can also include one-owner relationships, such as CUSTOMER makes PAYMENT. As information needs grew and as more sophisticated databases and applications were required, the network model became too cumbersome. Slide 34 Relational Data Model The relational model was introduced in 1970 by E. F. Codd (of IBM) in his landmark paper “A Relational Model of Data for Large Shared Databanks” (Communications of the ACM, June 1970, pp. 377−387). The relational model represented a major breakthrough for both users and designers. Its conceptual simplicity set the stage for a genuine database revolution. Slide 36 Relational Data Structure Relational models consist of a set of relations, each of which is represented as a normalized two-dimensional data table. In relational models, the entities and relationships of entities are represented as relations. Relations must meet the demands of some conditions, with the first and foremost being that each attribute is inseparable. Thus, a table cannot contain another sub-table in RDBSs. (Yue & Tan, 2018). Slide 37 Basic terms of relational data structure: Slide 42  A relational diagram A relational diagram is a representation of the relational database’s entities, the attributes within those entities, and the relationships between those entities. The relational diagram shows the connecting fields (in this case, AGENT_CODE). Slide 43 Linking relational tables SLIDE 44 E.g., RELATIONSHIP BETWEEN TABLES Lecturer: L. N. Commodore 45 Anatomy of a Relation Degree: Number of entities associated with a relationship Cardinality: Number of tuples Lecturer: L. N. Commodore 46 Examples of Relational DBMS MS Access Oracle MySQL Lecturer: L. N. Commodore Slide 49 Object Oriented Database A database that stores objects rather that data as individual relations. It make use of Object-Oriented languages such as C++ and Java. Object are organized into classes. A Class defines the way an object is structed. Objects can have inheritance relationships with other classes, allowing one object to contain the data of the new object inheriting it. Lecturer: L. N. Commodore Slide 50 Object Oriented Model Object-oriented data model (OODM)… Semantic data model Evolved to allow objects to also contain all operations. In OODM, an object is an abstraction of a real-world entity. They are the basic building block [for autonomous structures] Attributes are the properties of the object. Class: objects of similar characteristics. Classes are organised in a class hierarchy. Inheritance: an object within a class hierarchy inherits the attributes and methods of the class. OODB Classes ==> Tables Attributes ==> Fields Lecturer: L. N. Commodore Slide 53 Lecture Summary, Activities & Self Assessment Questions SLIDE 54 Summary of today’s Lecture Slide 55 Activity 4.1 Discuss any two (2) advantages and two (2) disadvantages associated with each of the major data models that have developed over the years. The evolution of DBMSs has always been driven by the search for new ways of modelling increasingly complex real- world data. Provide a chronological account of how the inadequacies of an existing data modelling approach are catered for by a newer one. Slide 56 Activity 4.2 Discuss the limitations of the traditional file processing system that led to the development of relational database management systems. You noticed that the minimart in your neighbourhood uses spreadsheet applications to keep its records. In not more than one paragraph, why will you advise them to use database management software instead of the spreadsheet application? Slide 57 Assignment 1 (Discuss at tutorial) The evolution of Database Management Systems (DBMSs) has always been driven by the search for new ways of modelling increasingly complex real-world data. Provide a chronological account of how the inadequacies of an existing data modelling approach are catered for by a newer one. Limit your discussion to the file management, hierarchical, network, and relational data models. Slide 58 The Entity Relationship Model NEXT LECTURE SESSION SLIDE 59 Slide 60  References Coronel, C., & Morris, S. (2017). Database systems: design, implementation, & management. (8th Ed). Nelson Education. Pratt, A. & Adamski, T. (2009). Introduction to Data Management. Chapter 1. Slide 61  INFS 329: Database Management Dr. Ebenezer Ankrah ([email protected]) Mr Lenin Commodore ([email protected]) 2022/2023 Academic Year Instructor: Mr. Commodore, [email protected] Lecture Outline The key topics to be covered in this session are: 01 02 03 04 05 Building Business Entity Demonstration Applications Blocks of Rules Relationships of ER for ER Entity & Integrity Modelling Modelling Relationship Constraints Modelling Slide 3 Recommended Text Coronel, C., & Morris, S. (2017). Database systems: design, implementation, & management. (8th Ed). Nelson Education. Slide 4 The Building Blocks of Data Models Topic 1 2022/2023 Academic Year SLIDE 5 The main building blocks/Elements of ERM Slide 6  Building Blocks of an E-R Diagram The ERD comprises three (3) main Components… Entity {Objects of the real-world; of “data interest”}. Attribute {a characteristic of an entity}. Relationship {describes an association among entities.} Constraints {Restrictions in the to ensure accuracy of data}. An entity relationship model is a graphical representation of entities and their relationships with each other. The model also identifies attributes of the entities and their constraints. There are conventions for representing the entities and attributes in the E-R Diagram. Lecturer: L. N. Commodore Slide 7 Types of Entities Tangible entities and Intangible entities Associative entities and Subtype entities Composite Entities ✔️ Strong entity and Weak entity ✔️ Further light will be shed on these, later in the slides. ✔️ For the sake of modelling, our discussion will be limited to strong entities, weak entities, and composite entities. Slide 8 Types of Relationships One-to-many (1:M or 1..*) relationship Many-to-many (M:N or *..*) relationship One-to-one (1:1 or 1..1) relationship Note that each of the relationship is bidirectional. Slide 9 Types of Relationships (Basic) (School of Advanced Study, 2022) Slide 10 Types of Attributes Simple Attribute: An attribute that cannot further be divided. E.g., the roll no of a student. Multivalued Attribute: A multivalued attribute can store more than one value in a single attribute. E.g., Phone Number. A person can have multiple numbers. Derived Attribute: An attribute whose values are calculated from other attributes. Composite Attribute: can be divided further into more than one attribute. i.e., an attribute can have further sub-attributes Slide 16 Attributes transform to [Database] Keys Some attributes or groups of attributes consequently serve as the database keys. A key refers to an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table (or relation). A key is also used when we want to establish relationships between the different columns and tables of a relational database. Slide 19 Types of Database Keys Slide 20 Secondary Key / Alternate Key A secondary key is an additional key, or alternate key, which can be use in addition to the primary key to locate specific data [or record] (IBM, 2021). Slide 25 Composite Key Slide 27 Compound Key (a form of composite key) Slide 28 Compound Key and Composite Key A compound key is similar to a composite key in that two or more fields are needed to create a unique value. However, a compound key is created when two or more primary keys from different tables are present as foreign keys within an entity. The foreign keys are used together to uniquely identify each record. https://www.bbc.co.uk/bitesize/subjects/zwd7pv4 Slide 29 Constraints A constraint is a restriction placed on the data. A rule that is used for optimization purposes. Constraints are important because they help to ensure data integrity. Constraints are normally expressed in the form of rules. For example: An employee’s salary must have values that are between 6,000 and 350,000. A student’s GPA must be between 0.00 and 4.00. Each class must have one and only one teacher. A student’s final score should not exceed 100%. Slide 33 Integrity Rules /Constraints Constraints are used to limit the type of data that can go into a table (w3schools, 2023). This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted (w3schools, 2023). Constraints can be column level or table level. Relational database integrity rules are very important to good database design. Many (but by no means all) RDBMSs enforce integrity rules automatically. However, it is much safer to make sure that your application design conforms to the entity and referential integrity rules Slide 34 Types of Integrity Rules An Integrity Rule is a rule that states that either each foreign key value must match a primary key value in another relation, or the foreign key value must be null (Hoffer, Venkataraman, & Topi, 2016). Slide 35 An Illustration of Integrity Rules Slide 36 How can the DB designer identify these building blocks? How do you properly identify the building blokcs (i.e., entities, attributes, relationships, and constraints) for your E-R diagrams? The first step is to clearly identify the business rules for the problem domain you are modeling. Slide 37 Business Rules Topic 2 2022/2023 Academic Year SLIDE 38 Business Rules & Database Design When database designers go about selecting or determining the entities, attributes, and relationships that will be used to build a data model, they might start by gaining a thorough understanding of what types of data are in an organization, how the data are used, and in what time frames they are used. But such data and information do not, by themselves, yield the required understanding of the total business. Slide 39 Business Rules & Database Design From a database point of view, the collection of data becomes meaningful only when it reflects properly defined business rules. A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization. A properly written business rules can be used to define entities, attributes, relationships, and constraints. E.g., Any time you see relationship statements such as “an agent can serve many customers, and each customer can be served by only one agent,” you are seeing business rules at work. Slide 40 Discovering Business Rules Business rules are derived from a detailed description of an organization’s operations. The main sources of business rules are company managers, policy makers, department managers, written documentation such as a company’s procedures, standards, or operations manuals. Slide 41 Discovering Business Rules A faster and more direct source of business rules is direct interviews with end users. Some of the sources of discovering the business rules can be reliable than others. It is the job of the database designer to resolve varying views arising from interviews with several persons [who may even be performing the same job]. Slide 42 Can all Business Rules be Modelled? Of course, not all business rules can be modeled. For example, a business rule that specifies that “no pilot can fly more than 10 hours within any 24-hour period” cannot be modeled. Another examples is that an Uber driver cannot driver more than 12 hours in a day. However, such a business rule can be enforced by application software. Slide 43 Translating Business Rules into Data Model Concepts. As a general rule, a noun in a business rule will translate into an entity in the model, and a verb (active or passive) associating nouns will translate into a relationship among the entities. For example, the business rule “a customer may generate many invoices” contains two nouns (customer and invoices) and a verb (generate) that associates the nouns. From this business rule, you could construe that: Customer and invoice are objects of interest for that environment/ domain and should be represented by their respective entities. There is a “generate” relationship between customer and invoice. Slide 44 Translating Business Rules into Data Model Concepts. As a general rule, to properly identify the relationship type, you should ask two questions: How many instances of B are related to one instance of A? How many instances of A are related to one instance of B? For example, you can assess the relationship between student and class by asking two questions: In how many classes can one student enroll? Answer: many classes. How many students can enroll in one class? Answer: many students. Therefore, the relationship between student and class is many-to-many (M:N). We will explore these relationships further in subsequent topics. Slide 45  THE ENTITY RELATIONSHIP MODEL TOPIC (ERM) 3 CHEN CROWFOOT UML 2022/2023 Academic Year 46 The Entity Relationship Model (ERM) The Relational Database Model, that the ERM forms the basis of an ERD. The ERD represents the conceptual database as viewed by the end user. ERDs depict the database’s main components: entities, attributes, and relationships. Because an entity represents a real-world object, the words entity and object are often used interchangeably. Slide 47 ERM The Entity-Relationship (E-R) model is a highly conceptual data model developed by Chen in 1976 to facilitate database design. The main purpose of developing a high-level data model is to support end user’s perception of the data and to conceal the more technical aspect associated with database design. Lecturer: L. N. Commodore Slide 48 ERD The E-R model forms the basis of the E-R diagrams. It is used to model and design relational databases. The ERD represents the conceptual view of the database. ERD expresses the logical structure of a database graphically. Is a visual form of relational databases. Lecturer: L. N. Commodore Slide 49 Notations for E-R Modelling/Diagrams Because of its The Chen notation favors conceptual implementation modeling. emphasis, the Crow’s Foot notation can represent only what could The Crow’s Foot notation favors a more be implemented. implementation-oriented approach. The UML notation can be used for both conceptual and implementation modeling. Slide 50 Conventions for ERD using Chen’s Notation Entities are represented by a rectangular box with the name of the entity in the rectangular box. An attribute is show with an eclipsed-shape attached to the relevant entity using a line; and labeled with the attribute’s name. The entity name is written is uppercase whereas the attribute name is written in lowercase. The primary Key/key attributes are underlined. Lecturer: L. N. Commodore Slide 52 Conventions for ERD using Chen’s Notation The attributes are connected by drawing lines to the entity. If the attribute is simple or single valued, a single line is used. If the attribute is multivalued, then a double line is used. If the attribute is derived, a dotted line is used. If the attribute is composite, its composite attributes are show as ellipses emanating from the composite attribute. Lecturer: L. N. Commodore Slide 53 Symbols used in ERD SYMBOL USE Entity Attribute Link Relationship Lecturer: L. N. Commodore Slide 54 Notations for E-R Diagram Lecturer: L. N. Commodore Slide 55 Modelling Entities and its Attributes Slide 58 Mapping Process (Algorithm) Create table for each entity. Entity's attributes should become fields of tables with their respective data types. Declare primary key. Lecturer: L. N. Commodore Slide 59 Mapping Relationship Lecturer: L. N. Commodore Slide 60 Types of Attributes A composite attribute, not to be confused with a composite key, is an attribute that can be further subdivided to yield additional attributes. For example, the attribute ADDRESS can be subdivided into street, city, state, and zip code. Similarly, the attribute PHONE_NUMBER can be subdivided into area code and exchange number. A simple attribute is an attribute that cannot be subdivided. For example, age, sex, and marital status would be classified as simple attributes. To facilitate detailed queries, it is wise to change composite attributes into a series of simple attributes. Slide 61 Types of Attributes (Cont’d) Modelling Composite Attributes Lecturer: L. N. Commodore Slide 62 Types of Attributes (Cont’d) A single-valued attribute is an attribute that can have only a single value. For example, a person can have only one Social Security number. Multivalued attributes are attributes that can have many values. For instance, a person may have several college degrees, and a car’s color may be subdivided into many colors (that is, colors for the roof, body, and trim). In the Chen ERM, the multivalued attributes are shown by a double line connecting the attribute to the entity. The Crow’s Foot notation does not identify multivalued attributes. Slide 63 Types of Attributes (Cont’d) A multivalued attribute in an entity Slide 64 Types of Attributes (Cont’d) Splitting the multivalued attribute into new attributes Slide 65 Types of Attributes (Cont’d) A derived attribute is an attribute whose value is calculated (derived) from other attributes. The derived attribute need not be physically stored within the database; instead, it can be derived by using an algorithm. For example, an employee’s age, EMP_AGE, may be found by computing the integer value of the difference between the current date and the EMP_DOB. Microsoft Access formula → INT((DATE() – EMP_DOB)/365). Microsoft SQL Server → SELECT DATEDIFF(“YEAR”, EMP_DOB, GETDATE()) Oracle → SYSDATE instead of DATE(). Similarly, the total cost of an order can be derived by multiplying the quantity ordered by the unit price. A derived attribute is indicated in the Chen notation by a dashed line connecting the attribute and the entity. The Crow’s Foot notation does not have a method for distinguishing the derived attribute from other attributes. Slide 66 Types of Attributes (Cont’d) Depiction of a derived attribute Slide 67 Modelling the Relationships Slide 68  Terminologies Associated with Entity Relationship Modelling Lecturer: L. N. Commodore 2022/2023 Academic Year Slide 69 Terms Associated with Entities and Relationships Degree Connectivity Cardinality Dependency Participation Lecturer: L. N. Commodore Slide 70 Degree of Relationship The number of entities associated with a relationship. Lecturer: L. N. Commodore Slide 71 Connectivity & Cardinality Connectivity: This term is used to describe the relationship classification in an ERD. Mapping cardinality or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. Mapping Cardinalities are most useful in describing binary relationship sets. Typically, for binary relationship between the given entities X and Y, the mapping cardinality must be as follows. Lecturer: L. N. Commodore Slide 72 Cardinality ratio or mapping cardinalities Cardinality ratio or mapping cardinalities is a concept that describes binary relationship set (a relationship that connects two entity sets) and its types. It is about the maximum number of entities of one entity set that are associated with the maximum number of entities of the other entity set. (exploredatabse, 2017) Slide 73 Cardinality One to One An entity X is associated with at most one entity in Y, and vice versa. An entity X is associated with at most any number of entities in Y. One to Many But Y can only be associated with one entity in X. An entity X is associated with a number of entities in Y; and vice Many to Many versa. Lecturer: L. N. Commodore Slide 74 Enforcing Cardinality Rule in E-R Modeling Lecturer: L. N. Commodore Slide 75 Dependency Entities are classified as strong or weak entity types or existence-dependent. Existence Dependency means that the existence of an entity: X, depends on the existence of another entity Y. Operationally if Y is deleted so is X. Y is the Dominant Entity and X the Subordinate Entity A strong entity is not existence dependent (Parent/Owner/ Dominant Entities). A weak entity is existence dependent. (Child dependent/ Subordinate Entities) Lecturer: L. N. Commodore Slide 76 Weak & Strong Entities Lecturer: L. N. Commodore Slide 77 Participation An entity can participate in a relationship wholly (totally) or partially. Alternatively called: Mandatory or Optional participation A condition in Mandatory If the existence Optional which the other of an entity participating requires the entity may or may existence of an not be associated associate entity with the in a particular occurrence of the relationship optional entity in the relationship. Lecturer: L. N. Commodore Slide 78 Representing Optional and Mandatory Entities on ERD An optional entity is shown by drawing a small circle on the side of the optional entity. Lecturer: L. N. Commodore Slide 79 Composite Entities To translate the E-R model into a relational database model, it is required to transform the E-R models into a one-to-many relationship. Meaning many-to-many relationship will have to be broken down into one-to-many relationships. Lecturer: L. N. Commodore Slide 80 Composite Entity: Work Lecturer: L. N. Commodore Slide 81 Decomposition Lecturer: L. N. Commodore Slide 82 Transforming ER Diagrams into RDB 2022/2023 Academic Year SLIDE 83 Mapping the Elements of ER Diagram to the Components of a RDBMS Entity → Tables Attributes → Columns Slide 84 Mapping Process Create table for a relationship. Add the primary keys of all participating Entities as fields of table with their respective data types. If relationship has any attribute, add each attribute as field of table. Declare a primary key composing all the primary keys of participating entities. Declare all foreign key constraints. Lecturer: L. N. Commodore Slide 85 Characteristics of a Relational Table 1. A table is perceived as a two-dimensional structure composed of rows and columns. 2. Each table row (tuple) represents a single entity occurrence within the entity set. 3. Each table column represents an attribute, and each column has a distinct name. 4. Each row/column intersection represents a single data value. 5. All values in a column must conform to the same data format. 6. Each column has a specific range of values known as the attribute domain. 7. The order of the rows and columns is immaterial to the DBMS. 8. Each table must have an attribute or a combination of attributes that uniquely identifies each row. SLIDE 86 Summary ER model is a high-level ER model or data schema conceptual data model map or data map is developed by Chen in another means of 1976. capturing data and their organization ER model is based on real ERD captures the entities, world situation that consist of their attributes and the a set of basic objects called entities and their attributes; relationship between the and the relationship among entities. This is done these objects or entities. pictorially. Lecturer: L. N. Commodore Slide 87 Activity 5.1 To learn how to create ER diagrams with the help of any of the following: Microsoft Visio Lucidchart Smartdraw Creately (https://app.creately.com/)  we will use this. Slide 88 Works Consulted Coronel, C., & Morris, S. (2017). Database systems: design, implementation, & management. (8th Ed). Nelson Education. Hoffer, J., Venkataraman, R., & Topi, H. (2016). Modern database management. Pearson Education Limited. ISRD Group. (2006). Introduction to Database Management Systems. Shahbaz, Q. (2015). Data mapping for data warehouse design. Elsevier. https://www.visual-paradigm.com/tutorials/databasedesign.jsp https://www.tutorialspoint.com/dbms/er_model_to_relational_model.htm Lecturer: L. N. Commodore Slide 89 Slide 90 INFS 329: Database Management Dr. Ebenezer Ankrah ([email protected]) Mr Lenin Commodore ([email protected]) 2022/2023 Academic Year Lecture Outline The key topics to be covered in this session are: 01 02 03 04 05 Overview of History of Significance SQL Data SQL SQL SQL of SQL Types & Commands Literals Slide 2  Topic SQL – An Overview One (1) What is SQL? | A brief History of SQL | Importance of SQL. Lecturer: L. N. Commodore 2022/2023 Academic Year Slide 3 Introduction SQL is the standard language used by RDMS to interact with the database. It enables users to: Create tables Query the DB for an information or data. Modify data in the DB Delete data from the DB Define/grant access to specific data Different vendors (Microsoft, Oracle, etc.) use different flavours dialects of SQL. But they all have the same basic feature (or base). The concepts are the same. These days there are many software tools that can generate SQL commands for the user. Lecturer: L. N. Commodore Slide 4 A Brief History of SQL Donald D. Chamberlin and Raymond F. Boyce developed the first version of SQL at IBM. SQL was en