IntroductionToDB Concepts NCV CS IT (1)

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Bertie Buitendag Edited by Fotiene Avrakotos Downloaded from hlayiso.com Open Rubric

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Foreword The following set of notes comprising of two lessons covers various SOs and LOs from the subjects Computer Programming Levels 3 and 4 and Systems Analysis and Design Levels 3 and 4 as part of the NCV Computer Programming and Information Technology programme. There is a cross curricular overlap between some of the SOs and LOs prescribed as part of the aforementioned subjects. In some cases, the SO and LO are covered in full within the text and in other cases partially. The intention of these notes is not to substitute any existing text and prescribed textbook but should be regarded as additional supportive learning and teaching material. The table below presents a summary of some of the partial SOs and LOs covered in these lessons. It should also be noted that some of the content presented as part of these lessons are for further enrichment and clarification and are not necessarily part of any of the required SOs and LOs for the applicable subjects at hand, Systems Analysis and Design Computer Programming Level 3 Level 3 SO 1.1 Describe data processing and SO 5.1 Plan and design a database to provide a Information systems. solution to a given problem LO 6.1.1 Define the term model. LO 6.1.2 Define the term modelling. SO 6.3 Demonstrate an understanding of the concept of data modelling. Systems Analysis and Design Computer Programming Level 4 Level 4 LO 4.2.8 Different techniques used for describing SO 5.1 Design database access for a computer data structures are identified and application explained. LO 4.4.1 Different techniques used for describing data structures are identified and explained. LO 4.4.2 Analyse various source documents (or descriptions) towards the identification of entities, attributes and relationships LO 4.4.3 Differentiate between different types of relationships (Range: 1:1, 1:M, M:N) Downloaded from hlayiso.com 1

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers LO 4.4.4 Differentiate between different types of entities Range (Strong Entity, Weak Entity and an Associate Entity (Bridging entity)) LO 4.4.5 Demonstrate how M:N relationships are catered for in relational modelling and design LO 4.4.6 Differentiate between different types of keys and their purpose. (Range: Primary Keys, Super Key, Alternate key, Composite Key, Foreign Key) LO 4.4.7 Differentiate between the Chen and crows foot Notation LO 4.4.8 Construct an ERD as part of the analysis process or scenario indicating various keys, relationships, attributes as well as the possible cardinalities LO 4.4.9 Adapt (modify) an existing ERD to new specifications LO 4.4.10 Interpret and ERD LO 4.4.11 Demonstrate the concept of normalisation within the design of an ERD (Max 3rd Normal form) I trust that this will be a valuable resource in your own study endeavours and development as a TVET scholar. The author Downloaded from hlayiso.com 2

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Contents 1. LESSON: Introduction to Database (DB) concepts ................................................................................ 6 1.1 Living a world driven by information ............................................................................................... 7 1.1.1 Information systems and the organisation ................................................................................. 7 1.1.2 Information systems ....................................................................................................................... 9 1.2 Database organisation ................................................................................................................... 11 1.2.1 Data .............................................................................................................................................. 11 1.2.2 Information ................................................................................................................................... 12 1.2.3 Field ............................................................................................................................................... 12 1.2.4 Record .......................................................................................................................................... 13 1.2.5 Table ............................................................................................................................................. 13 1.2.6 Database ..................................................................................................................................... 14 1.3 The information cycle ..................................................................................................................... 14 1.4 Data organisation ........................................................................................................................... 15 1.4.1 Elements of data organisation................................................................................................... 15 1.4.2 Data warehouses and data marts ............................................................................................ 16 1.5 Components of a DB system environment .................................................................................. 17 1.5.1 Data .............................................................................................................................................. 17 1.5.2 Users .............................................................................................................................................. 18 1.5.3 Hardware...................................................................................................................................... 18 1.5.4 Software........................................................................................................................................ 19 1.5.5 Procedures ................................................................................................................................... 19 1.6 The Database Administrator and other users as part of the DBMS operating environment . 20 1.6.1 The Database Administrator (DBA) ........................................................................................... 20 1.6.2 The functions of the DBA and his or her interaction with other IT staff members ................ 20 1.7 Common DBMS functionality and advantages .......................................................................... 21 1.7.1 General tasks of the DBMS ......................................................................................................... 21 1.7.2 Accessing data in the DB for processing though the DBMS .................................................. 22 1.7.3 How the DBMS facilitate access to a DB .................................................................................. 22 1.7.4 Functions of DBMS ....................................................................................................................... 23 1.7.5 Additional DBMS functions and benefits .................................................................................. 27 1.8 Components of a DBMS ................................................................................................................. 28 Downloaded from hlayiso.com 3

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.9 Lesson Bibliography ......................................................................................................................... 30 2. LESSON Relational DB Concepts and design principles ...................................................................... 31 2.1 The relational DB .............................................................................................................................. 32 2.1.1 Structures in a relational DB........................................................................................................ 32 2.1.2 Relationships................................................................................................................................. 38 2.1.3 How relational DB design help to eliminate data redundancy ............................................ 39 2.1.4 The use, role and importance of keys in the relational DB environment ............................. 41 2.1.5 The concept of referential integrity and relational DB design .............................................. 44 2.2 Modelling a DB with ERDs ............................................................................................................... 46 2.2.1 The concept of business rules to derive entities and relationships ........................................ 46 2.2.2 Different types of relationships ................................................................................................... 50 2.2.3 Constructing a basic ERD ........................................................................................................... 53 2.2.4 Constructing a basic ERD from a source document .............................................................. 58 2.3 Principles of good relational DB design ........................................................................................ 63 2.3.1 Normalisation ............................................................................................................................... 64 2.4 Other data models.......................................................................................................................... 69 2.4.1 Object Oriented DB modelling and design ............................................................................. 70 2.4.2 Multi-dimensional DB designs ..................................................................................................... 70 2.5 Lesson Bibliography ......................................................................................................................... 72 Downloaded from hlayiso.com 4

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Downloaded from hlayiso.com 5

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1. LESSON: Introduction to Database (DB) concepts CONTENT LINK Database concepts •Use of databases •Importance of databases After completing this lesson, you should be able to: ✓ Define the term database ✓ Define the term database management software ✓ Describe the concept of a data hierarchy. (Range: Bits, bytes, fields, records, tables, databases, data warehouses, data marts) ✓ Describe the purpose of databases within the information cycle ✓ Describe common features that a database offer, in relation to the storage of data for processing ✓ Define the concept data integrity ✓ Describe the common components of a Database Management System ✓ Describe the common components of a database. Range: Tables, Queries, Views, Users including (User rights) ✓ Define the term transaction and describe how transactions effect the data that is stored and processed Downloaded from hlayiso.com 6

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.1 Living a world driven by information We live in a world driven by information and decisions that people make are based on the information provided to them. Just think about how easy it is to lookup your bank balance and to decide how to pay a particular account using online or cell-phone banking. Larger companies and organisations use data, which is collected in various ways to make decisions and change the way that they operate. If a retail company see that they have sold a big number of a certain item, then they could decide to order more. Companies utilise database (DB) applications (a form of information systems and decision support systems) to help them achieve their organisational goals. 1.1.1 Information systems and the organisation Data need to be organised in specific ways to be useful and to provide information for the user. When data is organised in a useful way, it can be used to obtain lots of results. One way of organising data is to plan, design and develop a database so that data can be manipulated to provide the user with meaningful results. To be able to provide these results a program (database application or information system) is needed to prepare results and reports requested by the user. Another definition: A DB can be described as a collection of data or facts regarding a specific topic, e.g. details of a CD collection or details concerning clients such as learner drivers. A database contains Database: A DB is a collection of tables of related facts or data (including resources or objects) that electronically stored, integrated -that is, cross-referenced - data allow us to use the database. that can be retrieved and We need to understand that DBs are used as part of an information manipulated to produce information. system. Scenario – Quick Banana Driving School Downloaded from hlayiso.com 7

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers An example is a system could be used by a driving school to manage the data of all potential learners, driving instructors and driving instruction bookings. Two figures of the driving school application appear below. Figure 1 Data management form - Instructors Figure 2 Data management form - Learners From the figures above we can see that the users of the system are allowed to browse and manage the data of the driving instructors (figure 1) and view the data of all the learner clients that the driving school has (figure 2). The system allows users to generate a report which shows which learners' licence has expired, as shown in Figure 3. Downloaded from hlayiso.com 8

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Figure 3 Example of an report 1.1.2 Information systems An information system is a powerful business tool used for day-to-day operations or to project important business decisions. This is done by analysing data stored within a database. Information System: Software An information system typically has the components presented in the that enables people to manage and organise data. It helps to next figure. answer questions and allow for better decision making. Data Information systems comprise • Raw unprocessed facts various components: Data, • Example: List of clients Software Processes, People and Communication Technology Processes (Procedures) • Organisational rules and tasks that govern the operations • Example: How an order is processed People • Various users that are involved in with the system • Examples: Managers, Users (Such as a cashier in a Point of Sale – POS system Software • Various programs that work with the hardware and storage to process the data • Examples: POS (Point of Sale System), College Management System Communication Technology (Network) • Technologies that allow the system to communicate with other components and other systems • Example, internet, internal network, routers etc. Figure 4 Components of an information system Downloaded from hlayiso.com 9

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers The data in a database (to which an information system connects) can be manipulated to enable the user to access and get the information required. A typical information system (such as the driving school application) allows users to: • Insert new data (E.g. Adding the data of a new learner (client) to the driving school system) • Update data (E.g. Changing the date of a lesson booking) • Query data (E.g. Determining which instructors are available for a lesson) • Delete data (E.g. Removing a learner (client) from the database) Downloaded from hlayiso.com 10

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.2 Database organisation In order to fully understand the value of a database we must first know how a database is constructed and organised. A database typically comprises the following components: 1.2.1 Data In all walks of life, data has a value of its own. Each organisation has certain resources that gets utilised to meet certain goals and objectives. Data are raw facts that do not mean anything by itself, but with the proper manipulation it is a valuable business asset. For Data: Raw, unprocessed facts. example, your name is a piece of data, but it does not hold any value These raw facts are processed into a useful and meaningful by itself. If you combine your name and surname, you are able to form to produce information. identify yourself. Combining those two pieces of data gives more purpose than separating the two pieces. Instructor Data Data Name Surname DOB Barbara Smith 1971/10/02 Data is the Latin word that David King 1965/02/15 means facts and is in plural Michael Young 1984/05/25 form. The singular form of data is datum which translate to fact. In the Afrikaans language to ask Figure 5 Examples of data the date is to ask a fact. Today’s date is a fact. Data is a resource since its essential to the operation of an “Wat is vandag se datum?” organisation, and should follow to the following points. ✓ Shareable: The same data should be available to multiple people. ✓ Transportable: Data should be able to be transferred from one point to another so that decisions can be made. E.g. faxing through documentation. ✓ Secure: Data should be protected from destruction and unauthorised users. ✓ Accurate: Data should be reliable and precise. E.g. data held by airlines has to be precise in order to facilitate correct bookings, flight-schedules, etc. ✓ Timely: Data should be current and up to date, because: o Strategic planners need historical data to plan. o Operational managers need current, “real time" data to make decisions. ✓ Relevant: Data should be appropriate in order to make decisions. Downloaded from hlayiso.com 11

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.2.2 Information Information is the strategic combination and manipulation of data so that it has value to the person that owns the data. Thus, information is data in a processed form. The financial statements of a company are an example of information. The content of the financial statements is derived from the data gathered throughout the year. This then forms a report which may be Data are converted into used to determine the financial stance of the company. information, by using a computer program, for Figure 6 shows an example of a report of all the learners whose example. Using knowledge, licenses expired from the driving school. The process forms part of a information is then interpreted to reach a conclusion. software application presented to the user and is one of the functionalities of the system. Report The Information Cycle relates to the constant storing (capturing) of data and the processing Data Process Information thereof to useful information • Learner client • Determine • List of learners which a company could use to data who’s licenses who’s licenses make informed decisions expired expired Figure 6 Examples of data The capacity to use this information is called knowledge. People use information to help them make decisions. For example, the driving school may see which learners’ licenses will expire soon, then arrange for extra lessons and their driving licence test. 1.2.3 Field Within a database system, a piece of data is referred to as a field. For example, the “DOB” is called a field and stores the birthdate data which may look like, “1971/10/02”. In other words, a piece of data is A field, is also referred to as a labelled so that we can identify it. This type of identification is called a column in DB terms. Each field represents a single field. See Figure 7. attribute of an entity. Downloaded from hlayiso.com 12

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Instructor Field Name Surname DOB Barbara Smith 1971/10/02 David King 1965/02/15 Michael Young 1984/05/25 Figure 7 Example of a field 1.2.4 Record A record is the combination of more than one field. By carefully combining certain fields, it is possible to describe a record with the data available. For example, the record contains the “Name”, A Record, is also referred to as a “Surname”, and “DOB” of a driving instructor and this can easily be row in DB terms. Each row represents the described and identified using the data. attributes which describes a unique entity. Instructor Name Surname DOB Record Barbara Smith 1971/10/02 David King 1965/02/15 Michael Young 1984/05/25 Figure 8 Example of a record 1.2.5 Table A table is a collection of related records. Several instructors’ pieces of data arranged into records is grouped together to form a table. Table A record, is also referred to as a row in DB terms. Instructor Each row represents several entities of data to describe a Name Surname DOB unique entity. Barbara Smith 1971/10/02 David King 1965/02/15 Michael Young 1984/05/25 Figure 9 Example of a table Downloaded from hlayiso.com 13

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.2.6 Database A database consists of two or more tables that can refer to one another. These tables do not both contain the same data, but only a link or a relationship between one another. A DB is a collection of related tables. Database The recognised Instructors shape used to Bookings represent a DB is a cylinder. This could be due to the fact that drum or cylinder storage was used in the LearnerClients early days of computing. Figure 10 Example of a DB with 3 tables 1.3 The information cycle The information processing cycle speaks about how information and transaction processing systems allow users to capture data. This data is transformed into useful information. The data stored could be used in other processes to generate the required output. Input Information Storage Processing Process Cycle Output Figure 11 Information processing cycle Downloaded from hlayiso.com 14

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.4 Data organisation We have seen how a database is organised, but we could also present the organisation of this data in a hierarchy of layers. 1.4.1 Elements of data organisation The following elements represents the components of the data hierarchy: ✓ Bit: A bit - or binary digit - is either a 1 (''on") or a 0 (''off") in data representation. ✓ Byte: A collection of bits, operated and grouped as a unit. (Usually in groups of eight.) ✓ Character: A character (sometimes called a byte) is a single character and a single position of computer storage. It can consist of a letter, number or special character. Examples are A, 1, or *. A simple character is made up of bits (usually eight). ✓ Field: A field is a single item consisting of one or more logically related characters. (Related means that it has a common purpose). Examples include your last name, your enrolment number or your ID number. A field is sometimes called an attribute or a data element. A field may be Numeric or Alphanumeric. A Numeric field is composed of numbers. It is used for mathematical calculations. An Alphanumeric field made up of numbers, letters and other symbols (such as punctuation marks). Alphanumeric fields cannot be used for mathematical calculations. ✓ Record: A record is a collection of related fields. Examples inlcude all information on your report card or student card. A record is sometimes called an entity, the name of any object or concept about which an organisation wishes to store data. ✓ Table: A table is a collection of related records. Examples inlcude all the information of the learner clients of a driving school or students in a school. ✓ Database (DB): A DB is a collection of electronically stored, integrated (that means cross-referenced) data that can be retrieved and manipulated to produce information. Examples include the database used by a school of all the students, teachers, registrations, subjects offered and classes Downloaded from hlayiso.com 15

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers • Collection of related tables Database • Collection of related rows. Each Table (File) row represents an entity Smaller in size • Described by various fields (each field IN02 David King Record (Entity) represents an attribute of an entity) Larger in size Name • Attribute with a particular datatype. Fields David Field are normally made up of different characters • Represented by a collection of bytes. A character v Character could be numeric, alphabetic or a special symbols • Collection of bits 01110110 Byte • Bit – Binary digit, smallest amount of information that can be 1 Bit stored, either a 0 or 1 Figure 12 Simple data hierarchy database and its components 1.4.2 Data warehouses and data marts Organisations use a data warehouse (DW) to store and manipulate data from several sources (which could also be other databases) in a central warehouse (which could be a database) to support the A DB is typically designed and storage of very large amounts of data. One example could be the implemented to perform Online Transactional Processing (OLTP), transaction and call history of all customers in different countries of a and a DW for Online Analytical particular cell phone network provider. Processing. (OLAP). OLAP applications rely on the application of datamining The cell phone provider company can then perform various data techniques. analysis procedures to pick up patterns and trends for example on the A DB is normally managed by a use of the network and usage trends of customers. This may lead to Relational Database for example the role-out of new products and services to their Management System (RDBMS) and are concerned with real- customers. Big data is used. time data. A DW on the other Performing analysis on large amounts of data to discover new patterns hand mainly works with historical data, and trends is called data mining. Where a DW is a large collection of data and data sources, a data mart (DM) is a simple form of a DW, which stores less data and is more localised. E.g. For a cell phone company this could be only the data from a particular region. In the case of a large retail organisation, it could be the data of only one store instead of all the stores of the company. Downloaded from hlayiso.com 16

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers In order to populate a DW a process known as ETL (Extract Transform ETL is used to migrate data from one database to another, to Load) is applied. ETL has three different functions that a database form data marts and data warehouse system utilises to process the data from various databases warehouses and to convert databases from one format or and other sources into the DW. The transform process ensures that the type to another. (Beal, 2017) data read from the various sources conform to the design of the DW repository for the loading process. The load process involves the writing of the transformed data into the applicable DW repository. The next figure presents the typical architecture of a DW in an Moving data from one organisation. database to another or form one form of storage and format to another is called migrating the data. Opera- tional DB Company (Organisational) Data Data warehouse Customer DB Data Access and Analysis tools: Metadata ETL Process ▪ Queries Historical ▪ Reporting DB Raw Data ▪ OLAP ▪ Data mining Historic Text data based data (Flat files) External Data External Data Information directory Figure 13 Typical architecture of a DW 1.5 Components of a DB system environment There are five components in a database system environment which makes up an information system, (also see 1.1.2) i.e. 1. Data 2. Users 3. Hardware 4. Software 5. Procedures 1.5.1 Data We are familiar with the concept of data as raw facts and that data is the main resource in any information driven organisation. Data could however be managed and stored in different ways, think of storing Data redundancy, the data in text-files or in spreadsheets. The data contained therein could unnecessary duplication and storing of data which could be useful to you as a person, but it is normally difficult to manage and lead to problems such as data share amongst many people. anomalies. Downloaded from hlayiso.com 17

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Organisations choose to store their data in a DB. This approach differs from other systems in two distinct ways, i.e. a DB is integrated and shared. Integrated means that each application doesn't make use of it 's own files, but all files are shared amongst applications. This partly eliminates redundancy. Because of sharing, the same data can be accessed by all the applications at the same time. This is known as Concurrent sharing of data. Due to concurrent sharing, it is easier for systems to integrate. While one application enters data into a DB, another can access and update it. For example, while one person deposits an x-amount into your account, an application updates your balance. Simultaneously you can withdraw a y-amount from your account, and another application updates your balance. Your balance at the bank will thus be current. 1.5.2 Users The users of the system are the people that are involved with the day- to-day operations of the system. The users access the data and interact with the various user interfaces provided by the system to facilitate the operations of the company or organisation that they work with. There are three different categories of users in a DB environment: ✓ End Users: Interact with the DB by running application programs ✓ Database Administrators (DBA's): DB Administrators have the final control over the DB by controlling access to the DB. They decide whether a certain piece of data should be duplicated or not, thus controlling data redundancy. Not only must the DBA have knowledge of the DB itself, but also of the entire organisation and its requirements. ✓ Programmers: Develop programs and applications that use the data stored in the DB. 1.5.3 Hardware The hardware in a DB environment refers to all the physical components, servers, network hardware which enable the DB to run communicate and function. Downloaded from hlayiso.com 18

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.5.4 Software DB software can be divided into 2 major categories: 1) DBMS - A Database Management System (DBMS) is a comprehensive software tool (that allow users to create, maintain, and manipulate a database to produce useful information). Some popular examles of DBMS soltware include. MSAccess, Oracle, DB2, mySQL, Microsoft The DBMS software represents the interface between the user and the SQL Server. computer's operating system and database. Group - Developers DBGroup User Programmers Administrator & Designer SPECIALIST USERS Database Group Metadata DBMS END USERS Table Information flows Table & Data requests Table User User Figure 14 DBMS and its users 2) APPLICATION SOFTWARE - applications and programs written by the programmers for the end-users. Application software makes use of the DBMS software to add, delete or modify data held in a DB. 1.5.5 Procedures Procedures relate to the various operational instructions that manage the DB and the design thereof. The various users that operate and use the DBMS and the subsequent DB need instructions and guidelines on how to effectively run the DBMS. Some examples could include instructions on how to log in to the system, how to and when to perform backups, how to change the structure of the DB and how and what naming scheme is followed. Downloaded from hlayiso.com 19

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.6 The Database Administrator and other users as part of the DBMS operating environment The database administrator (DBA) plays a key role in the management and maintenance of the DBMS. 1.6.1 The Database Administrator (DBA) The fact that the data is so big leads to the need of management and control thereof. Hence, a need arises for what we call a Database Administrator (DBA). CHOOSING AN IDEAL DBA • The function of the DBA is For effective functioning of the DBMS, interaction with the various ultimately to control and entities such as end-users, systems analysts and programmers need to ensure smooth or take place. uninterrupted functioning of the DBMS applications. Each entity has their individual responsibilities, so a co-ordinator is • To achieve this goal, the DBA required to oversee all these functions, and this is the role of the DBA. should be able to negotiate with management as well as end-users, system analysts Being part of management, he or she should: and programmers. • The DBA's status should therefore be equivalent to Be able to gain trust and confidence from the end-users that of management in Know the functioning and intricate details of the organisation well. order for the DBA to have the necessary credibility to Be able to settle disputes and give effective solutions to negotiations give orders and to have between management and end-users. them fulfilled effectively. 1.6.2 The functions of the DBA and his or her interaction with other IT staff members The functions of the DBA ✓ Helps in the design of the DB CHOOSING AN IDEAL DBA ✓ Selecting security control • The function of the DBA is ultimately to control and ✓ Choosing ideal software and hardware ensure smooth or ✓ Maintaining the various DB entities uninterrupted functioning of the DBMS applications. ✓ Provide expert assistance • To achieve this goal, the DBA ✓ Ensuring efficient functioning of the DB should be able to negotiate with management as well as ✓ Maintaining a balance - between end-users & management end-users, system analysts and programmers. Downloaded from hlayiso.com 20

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers • The DBA's status should Tools of the DBA therefore be equivalent to that of management in Tools of the DBA would mainly include the DBMS itself, the data order for the DBA to have dictionary and many other entities used for maintenance, design and the necessary credibility to give orders and to have data manipulation. them fulfilled effectively. These tools enable the DBA to develop logical and physical data designs and structures. The interaction of the DBA with the 'IT' staff The DBA is a centralised figure who liaises both with management as well as the IT staff. The IT staff includes employees such as: ✓ Systems Analysts which ensure that the DBMS meets the organisation's daily requirements. ✓ Application Developers use standard programming languages and build systems. ✓ DB Designers are concerned with the technical aspects of the DB - designing structures to satisfy the organisation's needs. ✓ End-users are people who ultimately access the DB in the course of their daily work. 1.7 Common DBMS functionality and advantages To create, gain access to and modify the database, the special software known as the Database Management System (DBMS) is required. A DBMS may be thought of as a layer of software for accessing the database files. A (DBMS) is a collection of software that allow users to create, maintain, and manipulate a database to produce useful information. For example, if a user needs to access data stored in a DB, it is done through the DBMS. Refer to Figure 14 (DBMS and its users) on page 16. 1.7.1 General tasks of the DBMS A DMBS presents the following generic functions to its users: ✓ Allows users to set up their DB. ✓ Processes all data requests. ✓ Allows multiple users to access the same data stored in the DB. ✓ In the event of a system failure, it has backup and recovery facilities to return the DB to its original state prior to the failure. Downloaded from hlayiso.com 21

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.7.2 Accessing data in the DB for processing though the DBMS One of the main functions of a DBMS is to provide its users with a mechanism to access and retrieve the data contained within the DB that the DBMS manages. To accomplish this the DBMS provides a query language. A query language is a language that allows users to ask questions about or retrieve information from a database. (SQL is an example of this.) This language may be used interactively to retrieve information, or it may be embedded into a program written in Java, C++, Python, Delphi, Cobol or C for example. 1.7.3 How the DBMS facilitate access to a DB There is a relationship between the Operating System (OS) and the DBMS. The DBMS relies and is normally based in an OS to provide access and other management operations to the various files (including the data files) required by the DBMS. The OS is also known as the file-handling sub-system. The DBMS is not part of the OS, but interfaces with it. The DBMS can be found between the OS and the user programs. It provides a connection between the OS and the various application or information system programs. End users Database Operating DBMS Application System Programs SQL (Structured Query Language) is a standardised programming language used DBAs, for managing relational Developers, databases and performing Designers DBMS various operations on the data Interface in them. Initially created in the 1970s, SQL is used by database Figure 15 Relationship between the OS and DBMS administrators (DBA), as well as by developers writing data integration scripts and data analysts looking to set up and Accessing the DB run analytical queries. (Rouse, 2017) There are four steps involved when an application attempts to access the DB. Downloaded from hlayiso.com 22

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1. The application program decides which data it needs from the DB. This is normally something that a user requests, such as retrieving a list of all learner clients who have a driving lesson appointment today. This request is then put into the DBMS. 2. The DBMS does a validation check. It checks to see if the required data is available, and if the specific program (and user) has the necessary rights to access it. 3. The DBMS then sends the answer to the OS, which then tells the DBMS where and how to access the data. 4. The data is retrieved and sent back to the DBMS. The DBMS transforms the data into the form required by the application. The DBMS changes the user’s logical requests into a list of operations to A database transaction is a be executed and control the flow of data between files and logical unit of database operations which are executed applications. as a whole to process user requests for retrieving data or The organisation’s data is stored in a DB. It is the job of the DBMS to updating the database. (databasecompare.com, 2017) make sure that all users are able to have access and to share the data no matter what type it is, or where it is located in the DB. Transaction Processing The managing and processing or user requests where data is changed in the DB is referred to as a transaction. A DB transaction is a unit of work performed on the DB, which is managed by the DMBS. A transaction normally results in the change of the state of the database. For example, when a new learner driver client enrols at the driving school, his or her details need to be captured, validated and stored in the applicable tables in die DB. The tables as part of the DB was different before and after the new learner client was added, which resulted in a change of state to the DB. The change in the state is called transaction processing. Large company’s applications and programs manage millions of transactions per day, just think on how many transactions a cell phone provider network will generate if all the details of all the calls done by the customers are recorded. 1.7.4 Functions of DBMS There are seven generic categories of functions that a DBMS offer i.e. ✓ Provides access to data ✓ Ensures data independence ✓ Provides data protection ✓ Enables data standardisation ✓ Controls data redundancy ✓ Supports a specific data model ✓ Provides an on-line data dictionary for help Downloaded from hlayiso.com 23

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers We will now look at some of these in more detail. Access to data The first main function of a DBMS is to provide users with access to data stored in a DB. In a multi-user environment, the data must be able to be shared amongst users. Data sharing can lead to the following problem: Concurrency problem; i.e. when 2 applications have access to the same data. For example: On the 15th of each month, a royalty scheme program is executed. This program checks the current status of client's accounts and adds an amount of R100.00 to each account. Person A has an initial amount of R2000.00 in savings. While this royalty program is executing, Person A decides to withdraw R100.00 from his account, leaving a balance of R1900.00. The royalty program however adds the R100.00 to the R2000.00 (which was the original state of Person A’s account before the withdrawal), leaving a balance of R2100. The R100 should have been added to an amount of R1900.00 and not R2000.00 This results in a discrepancy in his account. It is situations like these that need to be controlled, especially where sharing of the same data is concerned. It is therefore imperative for DBMS software to control concurrent access to data. A DBMS supports multiple views of data stored in a DB. Each user may want a different view of the data. For example, the head of the human resource department may have access to salary information of all employees, while a normal clerk does not need to have access to these records. In a files-based system (or using spreadsheets for example), maintenance has always been a problem. In contrast, DBMS's are Remember that the application more accurate. Some DBMS's require that the system be shut down program interfaces with the DBMS and not with the data completely before any maintenance can be done. However, in most itself. So, if files were to be reorganised in a DB, it will have modern DBMS's, the system does not have to be shut down in order to no effect on the application do maintenance. Changes takes place dynamically. It can be programs that utilises it. immediately available to the users. The programmer doesn’t have to worry about the physical Data independence representation of data, and can rather focus on the logical The ability to use a database without knowing the representation representation of the data; i.e. the way the end-user sees it. details; i.e. what the format is, what the location is, and how it is accessed, is called data independence. This also implies that we just use the data in the tables, and not worry about the technical details. Downloaded from hlayiso.com 24

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Advantages of data independence ✓ The structure of data can be changed without affecting programs that use it. Thus, programs can be developed independently without having to worry about the underlying format of the data. ✓ The access methods used to find data are independent of programs as the DBMS decide which method is the best for a given command. Data protection Data is any organisation's major resource. Thus, certain measures should be taken to ensure its safety. People that are not authorised to access the data in the DB should not be allowed to use it. The DBMS should not allow them to access the data. Not all users have the same privileges. Authorised users should therefore have access only to that data for which they have privileges. The DBMS should be able to provide different levels of data access, right down to item level. For example, only managers and the head of the resource department should have access to sensitive data like salary information. This is accomplished by granting a user access using passwords or privileges. These mechanisms ensure the safety of data, as it is a precious resource. It is also much more susceptible to "attacks" than traditional files-based systems as all the data is kept in one central point, the DB. To keep the DB in a secure state, the DBMS must make sure that application programs that use the DB, are executed properly or not at all. For example: Assume you are busy transferring money from one account to another but while your transaction is being processed, something goes wrong, e.g. an error in connection. The DBMS must return the DB to the state it was in before the transaction took place. This is known as a role-back. The DBMS should have a log of all transactions and provide backup and recovery facilities to return the DB into its original state prior to any failure. One of the attributes of data is that it should be accurate at all times in order for it to be of any use. This is known as data integrity. Integrity is lacking if there are data inconsistencies. For example: The same data item stored in two locations with different values. A student with two different student numbers in separate files can be a typical example of this. Downloaded from hlayiso.com 25

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Certain rules need to be built into the DBMS or into the applications that use the data, to ensure data integrity. In this example, a solution could be to make sure that every student has a unique number. Data redundancy A DB is practically a massive storage device. When the same data is stored in multiple places in the DB, we end up with redundant data. If all users have access to the same data instead of each one having their own copy of it, data redundancy can be controlled. Each data item should be stored only once in the DB. Multiple users would then be able to gain access to the data, using the DBMS. All data redundancy is not necessarily eliminated in a DB. The DBMS may automatically update all duplicate copies of data to ensure that there are no data inconsistencies in the system. Data models The method that is used to represent data and to show the relationship between data is known as data modelling. One example of a database model is an ERD (Entity Relationship Diagram) – this diagram is used to draw the database components and the relationships between them in a graphical manner. Typically, the ERD will contain tables, relationships between the tables, the definition or list of fields within the table and also the types of relationships between the different tables. We will look at different topologies and specifically the relational database concept in our next lesson. There is however, three level views regarding the modelling of DBs and how it is seen by the DBA’s, developers and DBMS. These views (External, Conceptual and Internal) are depicted and described in the next figure. User Interfaces External How the data is presented to the user Reports • Forms of the data and how it is shown to the users in the various applications, reports etc. Objects Conceptual Represents the logical schema of the data Relations • Denotes the data that is stored and how the data is related. What is stored and how it is described. Data structures Tables Internal Represents the physical storage of the data Records • Denotes how the data is stored and how it is managed and accessed by the Fields OS and the DBMS Figure 16 Three views towards DBMS Downloaded from hlayiso.com 26

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Data Dictionary The DBMS should also support a data dictionary. The data dictionary The data dictionary contains a contains a description of the structure of the data used in the description of the structure of database. It describes what data is available, where, how it is used the data used in the database. It describes what data is and who is responsible for it. The data in the dictionary is called meta- available, where and how it is data, i.e. data about data. It holds information about the storage used, and who is responsible for it. structure of data items and their access rights. The transaction log is a complete record of activity affecting contents On-line Help Facility of a database during a Most DB's have an on-line help facility. It guides the end-user in getting particular period, such as one day. to know the system. Even experienced users tend to make use of it, especially if they require something unusual from the system. Help may be obtained via queries, editing or commands. 1.7.5 Additional DBMS functions and benefits Some of the other benefits and functions of a DMBS include: Data Standardisation The migration from a files-based system to a DB system requires The DBMS ensures that data kept in the DB conforms to certain that end-users will have to be standards. An on-line data dictionary is available in this respect. The trained to access the data in the DB. They will need to learn data dictionary contains a description of the structure of the data how to manipulate the data in used in the database. It describes what data is available, where and order for them to get the information required. The how it is used, and who is responsible for it. This type of information is internal functioning of the DBMS essential to users, programmers and the DBA. If data redundancy is is transparent to the end-user; controlled, and data standards are used, many of the problems i.e. they don't have to know how the DBMS functions. associated with files-based systems will be eliminated. Migration of data Data could be transferred from an existing files-based system to a DB. The DB would however be set up with the appropriate data structures. All redundant copies of data will have to be removed, in order to minimise data redundancy. This way, there should not be a problem to recognise which copy is the correct one for updating purposes. The DBA decides which copy is the correct one. The DBMS controls the flow of data to and from the DB. Centralised Security Downloaded from hlayiso.com 27

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers A DB system should provide a secure environment to its users; i.e. only authorised users should have access to it. Users only have access to objects that they own, or that they have privileges to (i.e. the mechanism, which controls the granting of rights to access DB objects). Privacy Control in a DB system is managed by means of a password system. 1.8 Components of a DBMS The DBMS is a specialised software application that manages the operations of a DB. The DBMS comprise of several components of which each presents several functionalities. A database management system (DBMS) is software that controls the storage, The typical components are represented in the next figure. organisation, and retrieval of data. Typically, a DBMS has the following elements: Kernel code This code manages memory and storage for the DBMS. Repository of metadata Query Language This repository is usually called a (QL) Design tools and data dictionary. DBMS utilities for creating Query language ✓ Tables This language enables Data Definition ✓ Query's applications to access the data. Language (DDL) ✓ Reports (Oracle.com, 2015) ✓ Relationships Data Manipulation ✓ User interfaces Language (DML) SQL presents its users with the Figure 17 Components of a DBMS capability to perform basic queries, define and create data structures and relationships (DDL) and to change and alter All application programs are not necessarily written in the same the content of existing data programming language. A variety of interfaces are provided by the stored in tables (DML). DBMS to communicate with these different programs. The DBMS uses optimisers to effectively gain access to data. They function like an expert system, using the knowledge programmed into them as well as the current state of the DB in order to access the data quickly. A query language is an easy-to- Previously it was the job of the programmer to determine the access use language that allows users to ask questions about or part to data. retrieve information from a database. SQL is an example of this. This language can be used The DBMS should cater for a variety of data manipulation facilities for interactively or embedded into batch, on-line and interactive processing. It supports 3 types of a high-level language like C++, languages; i.e. Query Language, Data Definition Language (DDL) and Delphi, C# or Java. Data Manipulation Language (DML). Query language is used to obtain results from a DB. The figure below shows a simple SQL statement which is performed on our sample database. Downloaded from hlayiso.com 28

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers SQL Query A report writer generates reports after a query language has been used to request Which instructors earn Database information from a DBMS system. Operating DBMS more than R140 000 This is a Management per year? Information Systems tool. It generates reports using the System information captured by the TPS (Transaction Processing System) Application a specialised Information Result System. Figure 18 Running a query The utilities part of the DBMS software provides users with a A DDL is used to define objects in a DB. number of facilities. A graphical interface displays data in a graphical form. Oracle, DB2 and A DML is used to manipulate these objects. A DML uses a non- SQL server are all examples of DBMSs that provide graphical procedural language to communicate with the DBMS. interfaces. Computer Aided (CAD) software are tools used With a non-procedural language, you only need to specify what data by users to assist them in you want and not how to get it - SQL is an example of a non- designing and building systems, for e.g. the designing of screen procedural language that is used in a relational DB environment. A layouts as well as data validation of screen items. DML can be classed as a high-level language. It can be used The performance of the DB is interactively, where you type in a command (like SQL) and the results monitored as well guarded from unauthorised use. get displayed immediately or embedded, where a command is coded in a high-level language like Java, C++, Delphi, Oracle PLSQL. A DBMS uses an interface to process these DML commands. Downloaded from hlayiso.com 29

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 1.9 Lesson Bibliography Beal, V., 2017 – What is ETL (Extract, Transform, Load)? Webopedia Definition. (2017). Retrieved May 10, 2017, from http://www.webopedia.com/TERM/E/ETL.html DatabaseCompare.com. 2017. What is database transaction? - SQL Server Comparison Expert. (n.d.). Retrieved May 11, 2017, from http://www.databasecompare.com/what-is-database- transaction.html Mukada, A., 1999. The ABC of Data Management. © Technikon Pretoria (Substantial content used as with permission from HoD) Oracle.com. 2015. Introduction to Oracle Database. (2015). Retrieved May 11, 2017, from https://docs.oracle.com/cd/E11882_01/server.112/e40540/intro.htm#CNCPT88781 Rouse, M., 2017. What is SQL (Structured Query Language)? - Definition from WhatIs.com. (n.d.). Retrieved May 10, 2017, from http://searchsqlserver.techtarget.com/definition/SQL Downloaded from hlayiso.com 30

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2. LESSON Relational DB Concepts and design principles CONTENT Different types of databases and database design • Different types of databases and topologies • Database design • Relational databases After completing this lesson, you should be able to: ✓ Describe and motivate the common design principles behind a relational database ✓ Differentiate between various types of database topologies. (Range: Relational, Object- Oriented and Multi-dimensional databases) ✓ Expand concepts relating to relational database design, use and management. (Range: Normalisation (overview and purpose) with regard to the reasons: Reduce data redundancy, limiting data anomalies. Where does un-normalised data come from? Analyse general documents, e.g. a till slips to identify possible data entities, Design/entities, keys, record organisation.) ✓ Describe the concept of a relationship ✓ Differentiate between different keys and their purpose ✓ Explain the purpose of relational DB design ✓ Describe the concept and purpose of referential integrity ✓ Describe the characteristics of a good database in terms of the design. (Range: Data integrity, data independence, data redundancy, data security, data maintenance) ✓ Design a relational database with tables in 3rd normal form ✓ Identify and implement various keys as part of the database design. (Range: Primary Key, Alternate Key, Concatenated key, Foreign Key) ✓ Draw or modify a simple Entity Relationship Diagram using the Chen or Crows foot notation, indicating the importance of the relationship between the entities, as well as the applicable fields and key indicators ✓ Discuss the concept and purpose of normalisation ✓ Differentiate between 1st, 2nd and 3rd Normal form ✓ Normalise a particular table design Downloaded from hlayiso.com 31

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.1 The relational DB We have seen that a DB is an organised collection of data. One definition of a DB is that a database consists of two or more tables that can reference one another in such a way that these tables do not both contain the same data, but only a link or a relationship between one another. Data are stored in two- dimensional tables also known as relations. These tables are The concept of a relational DB design and modelling has first been linked through a unique set of described by EF Codd in 1970 in a paper entitled "A Relational Model keys; i.e. attributes whose values uniquely identify each row in of Data for Large Shared Data Banks". the table. The rows are called The relational model is the most popular and widely used DB model tuples, i.e.one for each occurrence of the object, and and is supported by the majority of DBMS. the columns are called According to Oracle.com (2015), a relational database is a database attributes, i.e. each representing that recognises relationships between stored pieces of information. If a different data element or fact about the object that must be databases are relational, they conform to the relational model. The stored. The rows represent relational model has the following major aspects: records and the columns represent fields. The value of the data in a field must be atomic; Structures Well-defined objects store or access the data of a i.e. it must only have a single database. possible value. Operations Clearly defined actions enable applications to manipulate the data and structures of a database. Integrity rules Integrity rules manage operations on the data and structures of a database. We will look at some of the important concepts relating to the relational model next. 2.1.1 Structures in a relational DB In the previous lesson, we have seen that a DB comprise a set of related tables. A table is the most important structure within a relational database. A table (which is a two-dimensional structure) represents a set of rows (each row represents a record) and in relational DB terms a row is referred to as a tuple. The columns in a table relate to the attributes of each row and the data contained therein is referred to as fields. Let’s examine two tables from the QuickBanana driving school DB example. Downloaded from hlayiso.com 32

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Instructors table There are several driving instructors working at the driving school and for each instructor that we regard as a unique entity we could present and describe the entity by different attributes. One of the instructors at the driving school is Lerato, the figure below represents some of her attributes. PersID: InstLicenceCodes: InstBasicSalPA : INS005 EB,C1,A,EC1 R 126 600.00 InstSAID: 8001234553089 InstName: Lerato InstSurname : Mangumeng InstDOB: 1980/01/23 Figure 19 Entity described by attributes We can see that Lerato is described by different sets of data attributes. Figure 20 Instructors table If we examine the Instructors table, we can observe that there are 10 records (i.e. rows or tuples). Each of these represent a different instructor and each instructor has its own set of unique attributes. Downloaded from hlayiso.com 33

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Each instructor has a unique PersID field. Two instructors may not have the same PersID field value. Having a field (attribute) whose value must be unique for each row (or entity that it represents) is an important concept in relational DB design. Such a field is called a primary key. It may be possible for other field attributes to have duplicate values, such as two instructors with the same surname and same salary, but one of the fields, i.e. the PersID field must contain a unique value. If you are enrolled at the school, you would also have a unique student number for example. Your South African ID number, is used by all state department systems, is also unique to ever person. Your SAID number is the primary key in most state systems. The use of primary key fields plays an important part in relational DB design, as it enables the creation of relationships amongst tables. LearnerClients table This table represents an extract of the data of all the learner clients for the driving school. Various attributes are stored. Figure 21 LearnerClients Table Downloaded from hlayiso.com 34

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers From this table we can see that every learner record has a unique LearnerClient value. This field is the primary key field value for the table. There is no other learner client with the same LearnerClient value in that field. Tables are linked through a Note that there is a PreferredInstructor column as well. In this column, unique set of keys. the values of the instructor which the learner client prefer are stored. In the data we can see that Justine Claasen prefers to have Jackson Tshotshana (INS004) as her instructor. How did we get to that? If we look at the preferred instructor code for Justine we will see the value INS004. That is the instructor number for Jackson Tshotshana (Figure 22). From our observation (in Figure 21) we In the next figure there is a little can also see that Jackson has 10 learners which prefer him as an diagram embedded. This instructor. diagram is called and ERD (Entity Relationship Diagram), which is a model. Storing a primary key (PK) value as the value for another attribute in another table is the basic principle on which the relational database concept is built. One tuple (field represented in a record) could be related to another tuple (record or row in another table). We will look at ER modelling in From our LearnerClients table we can see that the PreferredInstructor more detail later in this lesson. In field is a foreign key (FK). A foreign key is a field that normally relates to the meantime, attempt to a primary key field in another table. comprehend what the diagram depicts by yourself. Study the following figure which indicates that one instructor could have many learner clients. For each entry of the PersID (PK) field there could be many entries of the same value in the PreferredInstructor (FK) field in the LearnerClients table. Downloaded from hlayiso.com 35

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers PK Instructor 1 M Learner- has Client FK PK Figure 22 Relational DB enabled by relationships and keys Extra example - College DB At the ABC College, each student that enrolls for a course must complete four subjects. The college example DB has room for some improvements that we will address at a later stage. One aspect to think about is how and if the DB design in its current form will cater for the notion that one student may do many courses. Table (Entity): Two dimensional structure consisting of rows and columns. Name of a table: Every table has a name that can be used to refer to e.g. STUDENT. Tuple: Row of the table (in the example the details of one student). It describes the occurrence of a single entity. Downloaded from hlayiso.com 36

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Attribute: Column of the table that has a distinct name (in the example e.g. student number (STNO), surname, etc.). All the values of an attribute are of the same data type and format. Primary Key: Every table has a unique primary key that uniquely identifies every row (in the example: student number). Order of rows and columns is of no significance. Adding a 2nd table to the database Redundancy: To work effectively, no redundancy is allowed in tables of a database. Primary Key: A table has only one unique primary key. In the table STUDENT it is STNO and in the table COURSE the primary key is COURSECDE. Foreign Key: A foreign key may be used to link a table of a database with another table in the database e.g. COUR in the STUDENT table is a foreign key that can be linked with CRSE in the COURSE table. The value of a foreign key matches the value of a primary key in another table. Notation: STUDENT (STNO, SURNAME, INIT, COUR) COURSE (COURSECDE, DESCRIPTION, COST) COURSE_SUBJECTS (CRSE, SUB1, SUB2, SUB3, SUB4) SUBJECT (SUBCODE, NAME) Downloaded from hlayiso.com 37

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Relationships: In the STUDENT table the details of one specific student will appear only once and in the SUBJECTS table the details of one specific course also appears only once. One course may have many enrolments of students. The primary and foreign keys enable the user to establish the relationship between the different tables and to ensure that repeated data entries are kept to the minimum. This example also contains an ERD diagram, do you know the meaning of the symbols? We will look at ER modelling in detail. 2.1.2 Relationships We have seen that the concept of a relational database is realised through the creation of relationships between tables (which represent entities) by the implementation of keys. Lets have a look at all the relationships applicable in our Quick Banana Driving School Database. When we examine the Drivebookings table (this table stores information regarding the lesson bookings by the various learner clients, we can see that there are two FK’s used in the table. When a learner makes a booking for a driving lesson, a unique booking number is generated (PK) the details of the learner is linked to the booking via the LearnerClientNr (FK), and details of the instructor assigned to the lesson is referenced by the InstructorNr (FK). Downloaded from hlayiso.com 38

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers PK PK FK PK FK FK Figure 23 Relationships in the QuickBanana DrivingSchool Database Let's examine the first record of the DriveBookings table below: We are able to lookup the LearnerClientNr and see that it was who booked a driving lesson with: We could do this because of the use of relationships. 2.1.3 How relational DB design help to eliminate data redundancy One of the primary objectives or relational DB design is to eliminate data redundancy. Data redundancy is defined as the unnecessary duplication of data items within a database. This results in a wasting of storage space, because the same item of data is being stored in more than one place. Because data is stored in more than one place, it is possible for inconsistencies to occur. Data redundancy affects the system and may cause problems when data items are being updated, as it makes it difficult to distinguish which record contains the correct information about a data item. Downloaded from hlayiso.com 39

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Let’s examine the following table, which is not based on a relational Certain principles guide the database design process. The design, but may have been used to store lesson bookings by clients. first principle is that duplicating information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that draw information from the database will also contain incorrect information. As a result, any decisions you make based on those reports will then be distorted. (Support.office.com 2017) Bookings Inconsistencies In the data Repeating groups of instructor names and surnames = Redundant data Figure 24 Examples of data redundancy and data inconsistency’s Downloaded from hlayiso.com 40

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers When we examine the content of the table, we can see some data redundancy. The name and surname of the instructor as well as the name and surname of the clients are stored for each lesson. We could also see that the names of the instructors are repeating (which could also be the case for the learner clients). When somebody makes a booking, it is easy to make a mistake as seen in Figure 24 where the same clients name is spelt differently. Discrepancies in data items such as in Figure 24 is an example of a data anomaly (inconsistency). When data redundancy exists, it can lead to data inconsistency. Occurrences of a particular data item should be the same, else data is inconsistent. The relational DB model eliminates this problem because the name of the instructor and that of the learner client in our example appear only once as a data entry in one of the tables. 2.1.4 The use, role and importance of keys in the relational DB environment Within the context of relational database design, it is impossible to have no redundancy at all. The field values as part of foreign keys in related tables are data that is repeated. Foreign key: value that uniquely The relational DB functions on the principle of controlled redundancy; identifies a row or record in i.e. sharing common fields. A primary key in one table appears again another (related) table, and it normally a primary key in the as the link (foreign key) in another table. related table. FK values repeating. [Controlled redundancy] Figure 25 Repeating FK values We will now examine some of the different types of keys and their purpose in the DB design and application process. Downloaded from hlayiso.com 41

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Primary Keys Each table must have a primary key, whose purpose is to solely identify each row of the table. The following notation will be used in writing down a table description. Some common differences between PKs and FKs The underlined attribute (field) indicates the key of the table. PK FK Uniquely Is a field in the identify a table that is record in the primary key in LEARNERCLIENTS (LearnerClientNr, LearnerName, LearnerSurname, table. another table LearnerID, LearnerTitle, LearnerCell, LearnerDriverCode, Cannot Can accept accept null multiple null PreferredInstructor, LearnersLicenceCode, LearnersLicenceExpirydate) values. value. By default, the Table not data in the sorted The key value must be unique since there should not be any duplicate table is sorted according to according to FK (without rows in the table. The PK field is underlined and all the other field the PK (due to index) (column) names of the table are listed separated with a comma the automatic index) between two brackets ( ). May only have May have one PK in a more than one table (PK may FK in a table be composed of more than one field) (Chauhan, 2012). Concatenated (Composite or Combined) Keys In certain tables, there may be no single unique attributes. If this is the case, the primary key must be a concatenation of more than one attribute. Let's examine our DriveBookings table again. Figure 26 PK for the Drivebookings table In the table design view of the Bookings table in Ms Access we can see that the PK field is In its original design the PK field is the booking number, as all of the indicated with a small key icon. values are unique. DRIVEBOOKINGS (BookingNr, InstructorNr, LearnerClientNr, LessonDate, LessonTime, LessonDuration) Downloaded from hlayiso.com 42

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers The designers of the DB and the table could have decided to implement a concatenated key. If we look closely then we will see that a combination of the (InstructorNr + LearnerClientNr + LessonDate + LessonTime) will together create a unique value as there is no duplication of the same instructor with the same learner client and the same lesson date and time. PK In the table design view of the Bookings table in Ms Access we can also adapt the design so that all four fields together form a key, i.e. Concatenated Key. The values combined in each of these fields will ensure that we have a unique row. Figure 27 Composete, Concetenate or Compond key for the Drivebookings table DRIVEBOOKINGS ( InstructorNr, LearnerClientNr, LessonDate, To recap, a primary key (PK) of a table is that attribute, or set of LessonTime, LessonDuration) attributes combined (to form a concatenated key), whose values uniquely identify each Candidate and Alternate Keys (fact) row in a table. The remaining attributes supply It may happen that more than one attribute within a table is unique. additional information about each fact. When this occurs, each of these attributes is called a candidate key, and one of them is arbitrarily chosen as the primary key. The remaining unique attributes are then called alternate keys. PK CK Two other terms for a concatenated key is: A composite key or a compound key. Figure 28 Example of a candidate key for the Instructors table Downloaded from hlayiso.com 43

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers INSCTRUCTORS (PersID, InstSAID, InstName, InstSurname, InstDOB, InstContactNr, InstLicenceCodes, InstBasicSal) If no two instructors have identical names, then either the InstSAID or InstName attribute could be chosen as the primary key of the table, since they are both candidate keys. If InstSAID is chosen as the primary key, then InstName becomes an alternate key, and vice versa. Secondary Keys A secondary key is an attribute other than the primary key that is chosen, strictly for the purpose of retrieving records. This key may consist of a single attribute or a combination of attributes. 2.1.5 The concept of referential integrity and relational DB design A foreign key is a key whose value matches a primary key in another table. A foreign key must have either a null value (i.e. an unknown value) or a value that matches the primary key value in another table to which it is related. This is referred to as referential integrity. Referential integrity ensures that an attribute will never have (or refer Every foreign key value has a matching value in the to) an invalid entity. Thus, if referential integrity exists, it will be corresponding primary key. impossible to delete a row in one table whose primary key has Referential integrity uses these to ensure that there are no matching foreign keys in another table. In other words, if the parent orphan records i.e. it prevents (primary key) exists in one table, and it has dependent children you from deleting related (foreign keys) in another table, it will be impossible to delete the records. (teach-ict.com, n.d.) parent. Example: Downloaded from hlayiso.com 44

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers PK When designing your own DB later using MS Access you will be able to set up referential integrity when you define your relationships PK FK Figure 29 Defining a relationship with referential integrity In the screenshot above, a relationship is setup between the Instructors and the DriveBookings table, and referential integrity is enforced. If INS002 (CJ Mahlangu) decide to leave the employment of the The type of the relationship is driving school, it will be impossible to delete his employment record if 1:M (one to many, which implies that one instructor may do there are still learners who have drive bookings that reference her as many drive bookings) their instructor. The bookings referring to the instructor (FK) must first be replaced with another instructor before CJ Mahlangu could be deleted. The same applies for the LearnerClientNr. We cannot just remove a learner for the LearnerClients table whilst the learner still has some lessons booked. With strict referential integrity, it would also not be possible to create a drive booking without having a valid InstructorNr and in this case a valid LearnerClientNr as well. Downloaded from hlayiso.com 45

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.2 Modelling a DB with ERDs Modelling plays a very important part in the ICT world. Designers, programmers and analysts use different types of models to represent the conceptual and physical designs of various parts of systems. Various representation and modelling tools exist to model parts of computerised systems. Examples include UML (Unified Modelling Language), Entity Relationship Diagramming, Data flow diagrams etc. One of the important parts of the design of a system is the modelling process. 2.2.1 The concept of business rules to derive entities and relationships A database represents the data environment of a computerised information system and the system is normally designed to perform a set of business-related functions. The design of the system and the subsequent business processes that it enables also follows certain rules, referred to as business rules. Business rules Business rules are the requirements which a company set in order for their operations to take place. Business rules determine how the various processes, policies and procedures apply to the people and other entities that are involved in the day-to-day business of the organisation. Within a DB environment, business rules could be used to determine and define the various entities, attributes, relationships and also constraints. Examples of business rules Within the context of our Driving School example, examples of business rules could be: - A learner may only book a driving lesson if he or she has a valid learner’s licence - An instructor may only take learners for lessons for which the instructor has the same licence - Bookings for lessons must be assigned to an instructor - A learner must have one allocated default instructor - One instructor may have several lessons per day Downloaded from hlayiso.com 46

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers - Each instructor is assigned to one vehicle From examining our business rules, it is possible to determine the various entities that is applicable in the domain. Entities and attributes An entity is simply a person, place, event, or thing about which an organisation intends to collect data. Each row of a table, technically referred to as a tuple, contains the data for a particular instance of the entity represented by the table. Each row can thus be thought of as representing a real-world fact. A row can be thought of as a DB record. Each entity has certain characteristics, which are referred to as attributes (or fields). An attribute or column represents a particular characteristic of the group of facts in each row. PersID: InstLicenceCodes: InstBasicSalPA : INS005 EB,C1,A,EC1 R 126 600.00 InstSAID: 8001234553089 InstName: Lerato InstSurname : Mangumeng InstDOB: 1980/01/23 Figure 30 Entity described by a row with fields (attributes) In table notation the entity is written and all the fields (attributes, are indicated in the two brackets. The primary key field is underlined) INSTRUCTOR (PersID, InstSAID, InstName, InstSurname, InstDOB,, InstContactNr, InstLicenceCodes, InstBasicSalPA) Entities and attributes may be modelled in different ways. For the purposes of this subject, we will only visit two different modelling techniques, i.e. the Crows foot notation and the Chen notation. Downloaded from hlayiso.com 47

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Chen notation (Entity and attributes) Microsoft Visio is a diagramming tool that is used by designers, engineers and developers to InstSAID InstName create a wide range of models PersID and diagrams. InstSurname InstDOB INSTRUCTOR InstContactNr InstBasicSalPA InstLicenceCodes Screen snippet of stencil shapes Figure 31 Chen notation representing one entity with attributes. from MS Visio to draw ERD’s in the Chen notation In the figure above the entity is represented as a singular noun within a rectangle. The various attributes are represented in oval, the key attribute is underlined. Crows foot notation (Entity and attributes) Screen snippet of stencil shapes INSTRUCTOR from MS Visio to draw ERD’s in PK PersID the Crows foot notation InstSAID InstName InstSurname InstContactNr InstLicenceCodes InstBasicSalPA InstDOB Figure 32 Entity with attributes in the Crows foot notation In the figure above a single entity is represented in a rectangle and all the field names listed. The primary key field is indicated. Downloaded from hlayiso.com 48

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Relationships The idea of a relationship between tables and that they are realised through the usage and implementation of Primary – Foreign key An Entity Relationship Diagram “partnerships” is the cornerstone on which the relational database (ERD) is a data model describing concept is based. how entities (concepts or things) relate to one another. Essentially, a conceptual or Relationships exist naturally between entities in real life scenarios, logical ERD will visually show how the terms in your database which is also evident in daily business operations. relate to one another. They are especially helpful in clarifying information models for relational The business rule for example: One instructor may teach many learner databases and helping business clients makes perfectly sense. If we closely examine the business rule users understand database then we would be able to identify two nouns connected by a verb. structures at a high level and without details. For example: (Brandenburg, 2017) Instructor teach learners Learner- Instructor teach Client noun verb noun entity relationship entity Figure 33 Entity (noun) Relationship (verb) Entity (noun) Example To model this concept database designers read the various business rules and identify all the nouns and the various relationships between the nouns by looking at the verbs that are involved. INSTRUCTOR LEARNER- Teach CLIENT INSTRUCTOR LEARNER- Teach CLIENT Figure 34 Chen and Crows foot notation of two entities and their relationship Downloaded from hlayiso.com 49

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers With the Chen notation, the relationship is indicated within a diamond shape connected to each entity and with the Crows foot notation as a line with the relationship as text. 2.2.2 Different types of relationships Between different types of entities, different types of relationships may exit. The basic relationship description between two entities is called the cardinality. This specifies how many instances (record/(s)) could A relationship indicates the relate to another instance (record/(s)) of another entity. association between the entities. The primary and foreign keys indicate the relationship There are three main types of relationships, which we will examine between entities. The one (1) in a relationship is usually the next. primary key and the many (M) is the foreign key. You must be able to read each relationship One to Many relationships from both directions. We work with three types of relationships: One of the most common types of conceptual relationships is the one one-to-one (1:1), one-to-many (1:M) and many-to-many (M:N). to many relationship. It fits very naturally into the relational model. The following sample tables are given to illustrate this concept. The lectures entity refers to a lecturer and the classes entity represents the time table of a lecturer. LECTURER (LEC_NR, NAME, OFFICE_NR, TEL) CLASS (CLASS_NR, TIME, ROOM, LEC_ID) 1 M LECTURER Presents CLASS Figure 35 One to Many (1:M) relationship in the Chen notation. LECTURER Presents CLASS Figure 36 One to Many relationship in the Crows foot notation The relationship between LECTURER and TIME_TABLE (CLASS) is one-to- many (1:M); i.e. for each row in table LECTURER there may be zero, one, or several corresponding rows in table CLASS. In other words the PK value of the Lecturer table (I,e LEC_NR), may appear many times as a FK value (LEC_ID) in the Class table. Downloaded from hlayiso.com 50

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers One to One relationship In some cases, there could be one single instance of an entity (record) which relate to only one other single instance of an entity (record). One such example could be that one lecturer has one office space. LECTURER (LEC_NR, NAME, OFFICE_NR, TEL) OFFICE (OFFICE_NR, BUILDING, CAMUS_CODE) 1 1 LECTURER assigned to OFFICE Figure 37 One to One (1:1) relationship in the Chen notation. LECTURER assigned to OFFICE Figure 38 One to One (1:1) relationship in the Crows foot notation. Many to many relationships It is possible to represent many to many relationships in the design of an ERD, but it is not possible for the DBMS to resolve such tables when The CROWs foot notation gets its name from the symbol which is designed and created in database software. used to represent the many side of a relation (cardinality) One such example could be that many students may register (enrol) for many subjects. This type of relationship is seldom used and is presented differently. Looking at the table notations, it would be difficult to determine where and how the foreign keys should be placed in each table. This type of relationship indicates that 1 or more records in an entity can be related to 1 or more records in another entity. STUDENT (STUD_NR, NAME, SURNAME, SAID, ADRESS, CONTACT_NR) SUBJECT (SUBJ_CODE, SUBNAME, PRICE) N M STUDENT Register SUBJECT Figure 39 Example of a M:N relation in the Chen notation. Downloaded from hlayiso.com 51

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers This relationship has to be resolved into 2 one-to-many (1:M) relationships with a third entity between the two main entities. The third entity has a composite primary key. The primary will be a combination of the main entity’s primary keys (and sometimes an additional field). STUDENT (STUD_NR, NAME, SURNAME, SAID, ADRESS, CONTACT_NR) STU_SUB_REG (STUD_NR, SUB_CODE, ENROL_DATE, MARK_OBTAINED) SUBJECT (SUB_CODE, SUB_NAME, PRICE) The STU_SUB_REG entity is referred to as a composite or bridge entity. 1 M M 1 STUDENT Enrol STU_SUB_REG Register SUBJECT 1 M M 1 STUDENT STU_SUB_REG SUBJECT Figure 40 Composite entity (two different examples) Chen notation. STUIDENT STU_SUB_REG SUBJECT PK STUD_NR register PK FK STUD_NR enrol PK SUB_CODE The symbol for a composite or bridging entity in Chen notation NAME PK FK SUBJ_NR SUB_NAME is SURNAME PK ENROL_DATE PRICE SAID MARK_OBTAINED For the purposes of this subject, a single extra entity could also ADRESS be used. CONTACT_NR Figure 41 Composite entity (two different examples) Crows foot notation with fields and keys indicated. From our Crows foot example in Figure 41 of the composite (or bridging) entity we can see that a composite key for the STU_SUB_REG entity is used which comprise the STUD_NR, SUBJ_NR and ENROL_DATE as a composite primary key. This is because a student could enrol for a subject more than once until he/she has obtained a pass mark. For the purposes of this subject, we will not focus on the Therefore the enrolment date (for example the year which the subject ordinality of the relationships is taken) is also stored to uniquely identify each row in the STU_SUB_REG between tables. Ordinality refer to the indication whether the PK table. must be present or not within the related FK record or not. Downloaded from hlayiso.com 52

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.2.3 Constructing a basic ERD In this section we will look at two examples of a description that relates to the later development of a system. A basic database should be created based on the design of an ERD. The basic steps to construct an ERD is shown in the following diagram. Step 1 - Identify the entities Identify the nouns by looking at the various roles and List the nouns tangible things of which data can be stored about. Step 2 - Identify the relationships Identify the various relationships by listing the Join two related nouns (entities) with a verb, which various nouns applicable in the scenario. conform to the description and or the requirements Step 3 - Determine the connectivity Determine the connectivity (cardinality) between Ask yourself, how many times is the (or could the) each relationship one entity be related to the other Step 4 - Identify attributes and keys For each entity identify the common attributes which Add subsequent foreign keys to fit the business rules uniquely describe the applicable entity and relationships Example 1 – Fascination events Fascination Production & Events (FPE) contacted Software Solutions to build a software application to handle the scheduling of events. Fascination Production & Events is a dynamic company specialising in the organisation of important and exciting events within the entertainment industry. Companies appoint a coordinator which liaises with FPE in the hosting and organisation of the event. The Events are organised by a coordinator, of which the company stores the Employee ID, the Name, Surname and Contact Number as well as the name of the company that the coordinator works for. Downloaded from hlayiso.com 53

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Amongst these events are road shows, live performances of bands, comedy events, sporting events, and theatre performances. A coordinator is responsible for arranging events. The company employs coordinators who handle the organisation of a specific event, which is held at a specific venue. FPE also appoints an employee to liaise with the coordinator. Events are hosted at various venues, and for each venue, details regarding the name (e.g. Coca-Cola dome), address, and the GPS coordinates are stored. Step 1 – Identify the entries (List all the nouns, and determine if the noun is dependent on the scenario and if it could be descried by attributes) From the example below, all possible nouns are highlighted and a table is presented which determines whether the noun is applicable to the business rule and the foreseen operations of the system or not. Our nouns become our entities. Scenario and highlighted nouns Fascination Production & Events (FPE) contacted List of nouns Applicable to Reason Software Solutions to build a software application to business rules handle the scheduling of events. Fascination Production Fascination No It is the hosting company details for the & Events is a dynamic company specializing in the Production & company itself is not required as part of organization of important and exciting events within the Events the business processes. entertainment industry. Companies appoint a Software Solutions No It is the development house, no details are coordinator which liaisons with FPE, in the hosting and required . organisation of the event. The Events are organised by a Company No No relevance to the business rules. coordinator, of which the company stores the Employee ID, the name surname and contact number as well as Events Yes Details are stored about the event. the name of the company that the coordinator work for. Industry No No relevance to the business rules. Amongst these events are road shows, live Coordinator Yes Details are captured the coordinator is performances of bands, comedian events, sporting part of the business process. events, and theatre performances. A coordinator is responsible for arranging events. The company employs Venue Yes Details of the venue is part of the business coordinators who handle the organization of a specific process event, which is held at a specific venue. FPE also Employee Yes The employee is part of the business appoints an employee to liaise with the coordinator. process Events are hosted at various venues, and for each venue details regarding the Name (e.g. Coca-Cola dome), address, and the GPS coordinates are stored. Figure 42 Generating a list of nouns Step 2 – Identify the relationships In this step the verbs related to each of the nouns are determined. The nouns will represent the entities and each verb must be applicable to an entity. In general, each verb will be associated to two entities (but it can happen that it is associated to a single entity as well. (For the purposes of this subject, we will always look for two entities). Downloaded from hlayiso.com 54

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Scenario and highlighted nouns Noun Verb Noun Description (business rule) Fascination Production & Events (FPE) contacted Events organised Coordinator Events are organised by a Software Solutions to build a software application to coordinator. handle the scheduling of events. Fascination Production & Events is a dynamic company specializing in the Venue hosts Events Events are hosted at various venues organization of important and exciting events within the entertainment industry. Companies appoint a Employee liaise Coordinator Employee liaises with a coordinator which liaisons with FPE, in the hosting and coordinator organisation of the event. The Events are organised by a coordinator, of which the company stores the Employee ID, the name surname and contact number as EMPLOYEE VENUE well as the name of the company that the coordinator work for. Amongst these events are road shows, live performances of bands, comedian events, sporting events, and theatre performances. A coordinator is responsible for arranging events. The company employs liaisons hosts coordinators who handle the organization of a specific event, which is held at a specific venue. FPE also appoints an employee to liaise with the coordinator. Events are hosted at various venues, and for each venue details regarding the Name (e.g. Coca-Cola dome), address, and the GPS coordinates are stored. COORDINATOR organised by EVENT Figure 43 Determining the relationships Our verbs related to our entities become our relationships. We could read and interpret our diagram as follows. ✓ Employee liaises with a coordinator. ✓ A coordinator organises an event. ✓ An event is hosted at a venue. Step 3 – Determine the connectivity (relationship type) Next we need to determine how one entity is related to one another in terms of the identification of keys. One way of looking at it is to ask yourself, how many times one record of the one entity will be related to another. In our diagram below we attempt to relate the two nouns by reading and creating a business rule, which is in line with the scenario. The relationship needs to make sense when read from both directions. If we are satisfied that our relationship types are in line with the scenario and the business rules we could add the indications between the relationships. Downloaded from hlayiso.com 55

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Noun Verb Noun Left to right Right to left Events organised Coordinator Many events an be arranged by One coordinator can organise one coordinator many events Venue hosts Events One venue can host many Many events could be hosted at events a venue. Employee liaise Coordinator One employee could work with Many coordinators could work many coordinators with one employee EMPLOYEE VENUE 1 1 liaisons hosts M M COORDINATOR 1 organised by M EVENT Figure 44 Determining the connectivity (relationship type) Step 4 – Identify the attributes and keys In this step we attempt to group all the applicable attributes from the scenario which describe the various entities. Each attribute must relate to the entity. Where available or described as part of the scenario, we use the given unique field description as a PK, such as the Employee ID from the scenario. We could rename the PK, and where one is not provided or described, we can create our own, such as the EventID as indicated in the table. We also examine the descriptions of each of the entities closely. From our text we could see that various types of events could be held at a venue, for which we could present a field EventType. In this field a description relating to the type of event could be stored, e.g. concert, comedy show, sport event etc. Based on our scenario, our relationships and the cardinalities we add applicable foreign key fields. For example, the PK field of the VENUE entity is VenID. This field value is repeated as a FK in the EVENTS table. If interpreted, it would read as: One venue (PK record reference) could host many events (Many occurrences of the same VenueID) in the EVENTS table. Downloaded from hlayiso.com 56

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers ERDs could be modelled differently, and one needs to evaluate all possible scenarios. In our example, it is assumed that one Coordinator will always work with one employee from the company for which the event is for. If this was not the case then we could have added an associative entity between the company employee and the coordinator. Doing this would have indicated that many coordinators could work with many employees from the same company. Scenario and highlighted nouns with some attributes Noun Applicable attributes Key field Fascination Production & Events (FPE) contacted Event EventName, EventType, EventDateTime EventID Software Solutions to build a software application to handle the scheduling of events. Fascination Production Venue VenName, VenAddress, VenGPS VenID & Events is a dynamic company specializing in the Employee EmpNum, Name, Surmame EmpNum organization of important and exciting events within the Coordinator CoordinatorID, Name, Surmame, ContactNr, CoordinatorID entertainment industry. Companies appoint a CompName coordinator which liaisons with FPE, in the hosting and organisation of the event. The Events are organised by PK FK a coordinator, of which the company stores the Employee ID, the name surname and contact number EMPLOYEE (EmpNum, Name, Surname, CoordinatorNr) as well as the name of the company that the PK coordinator work for. Amongst these events are road shows, live performances of bands, comedian events, COORDINATOR (CoordinatorID, Name, Surmame, ContactNr, sporting events, and theatre performances (different CompName) type of events). A coordinator is responsible for PK FK arranging events. The company employs coordinators who handle the organization of a specific event, which is held at a specific venue. FPE also appoints an employee EVENT (EventID, EventName, EventType, EventDateTime, CoordinatorNr, to liaise with the coordinator. Events are hosted at VenueID) various venues, and for each venue details regarding the FK PK Name (e.g. Coca-Cola dome), address, and the GPS coordinates are stored. VENUE (VenID, VenName, VenAddress, VenGP) Figure 45 Choosing and defining attributes and keys Finally, we could represent the ERD in the Crows foot notation, which include field and key field indications. Downloaded from hlayiso.com 57

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers COORDINATOR EMPLOYEE PK CoordinatorID PK EmpNum Name Name liasons Surname Surname ContactNr FK CoordinatorNr CompanyName organised EVENT VENUE PK EventID PK VenID EventName VenName hosts EventType VenAddress EventDateTime VenGPS FK CoordinatorNr FK VenueID Figure 46 FPE Crows Foot ERD 2.2.4 Constructing a basic ERD from a source document Systems analysts and database designers sometimes need to examine and study some of the various source documents of an organisation to construct an ERD. Examples of source documents could include: invoices, order documents, payslips, reports etc. Upon examining these documents, it is possible to get an idea of the company processes which the documents capture. Analysists are then able to derive various business rules from it. In the Figure 47, a typical sales invoice is presented. In order to analyse the document, we could follow the following steps: ✓ Step 1 – Analyse the business document to get an idea of what the document represents ✓ Step 2 – List all applicable fields ✓ Step 3 – Identify all key fields ✓ Step 4 – Group fields according to possible entities which could belong to the applicable key field. If the entity does not have a key create one. Downloaded from hlayiso.com 58

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers ✓ Step 5 – Determine the possible business rules from the source document and the entities created ✓ Step 6 – Determine the relationships and possible cardinalities ✓ Step 7 – Indicate the cardinalities and key fields to realise the relationships by constructing an ERD and representing the relations in table notation. ✓ Step 8 – Evaluate the ERD against the source document Figure 47 Example of a source document (Invoice) Downloaded from hlayiso.com 59

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Step 1 – Analyse the business document to get an idea of what the document represents Upon studying the invoice, we can see that the company JetSet monate Foods allows customers to place orders, which could be delivered at an address. Customers order items from a menu. Each order has items and some of the items are on weekly specials. The invoice contains the details of JetSet Monate Foods such as the company address and contact details at the bottom of the invoice. These details however will be the same for each invoice. We can also see that the total for each product ordered is calculated, as well as the invoice totals. We will exclude the address of the company from our database. Step 2 – List all applicable fields We will create an initial entity called invoice as it represents the whole document and list all the fields that are found on the document. INVOICE (InvoiceNum, OrderNum, OrderDate, CustomerCode, CustomerAddress , CustomerName, DeliveryAddress, DeliveryInstructions, Quantity, Code, Description, UnitPrice, Promotion Code) Step 3 – Identify all key fields All the possible key fields are underlined. The key fields could be used to identify various other fields (later entities or records in our database). Examples include: Customer Code and the Order Number. INVOICE (InvoiceNum, OrderNum, OrderDate, CustomerCode, CustomerAddress , CustomerName, DeliveryAddress, DeliveryInstructions, Quantity, Code, Description, UnitPrice, PromotionCode, DiscountPercentage) Step 4 – Group fields according to possible entities which could belong to the applicable key field. If the entity do not have a key - create one. Possible entities We determine from the source document which nouns could be used that will relate to the various keys. (Remember: Our nouns become entities) Downloaded from hlayiso.com 60

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers INVOICE (InvoiceNum) ORDER (OrderNum, OrderDate, DeliveryAddress, DeliveryInstructions) CUSTOMER (CustomerCode, CustomerAddress, CustomerName) MENUITEM (Code, Description, UnitPrice) PROMOTION (Code, DiscountPercentage) Step 5 – Determine the possible business rules from the source document and the entities created From our source document and the possible entities we have defined in the previous step we could write down the following business rules. CUSTOMER places an ORDER ORDER consists of various MENUITEMS. ORDER generates an INVOICE MENUITEMS can be on PROMOTION An order has many ITEMS and the MENU has many items as well. We have identified an entity for ITEMS. The items on the invoice refer to the different lines of food, which the customer has ordered. The presentation thereof implies that there are many items on the menu and the customer could order many of each. Step 6 – Determine the relationships and possible cardinalities From our basic business rules, we can now present the different types of relationships between the various entities, as depicted in the next figure. Entity Relationship Entity Connectivity CUSTOMER Places ORDER 1:M ORDER Generates INVOICE 1:1 ORDER Comprise of MENUITEM M: M MENUITEM Marked as (on) PROMOTION 1:1 Entity Relationship Entity Connectivity ORDER Comprise of (has) ORDERITEM 1:M ORDERITEM Refer to MENUITEM M: 1 ORDERITEM = Associative Entity (Bridging Entity) Figure 48 Determining the relationships and possible cardinalities When we define and evaluate the relationship between the ORDER and the MENUITEM entity we realise that it is a M:N (Many to Many) relationship. Downloaded from hlayiso.com 61

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers In reality many ORDERS can have many MENUITEMS, in order to model and implement this for a relational DB, we must create a bridging (or associate) entity. The name for the bridging entity is chosen by the designer but it should make sense in the context of the relationship. In our example the name ORDERITEM was chosen which properly fits and describes the scenario. Step 7 - Indicate the cardinalities and key fields to realise the relationships by constructing an ERD and representing the relations in table notation. INVOICE (InvoiceNum, OrderNum) ORDER (OrderNum, CustomerCode, OrderDate, DeliveryAddress, DeliveryInstructions) CUSTOMER (CustomerCode, CustomerAddress, CustomerName) MENUITEM (Code, Description, UnitPrice) ORDERITEM (OrderNum, Code, Quantity) PROMOTION (Code, DiscountPercentage) The CHEN ERD for the JETSET Monate foods scenario is presented below. DeliveryInstructio CustomerCode OrderNum OrderDate DeliveryAddress InvoiceNr OrderNum ns CustomerAddress CUSTOMER places ORDER generates INVOICE 1 M 1 1 1 CustomerName CustomerCode OrderNum has M Code ORDERITEM M Quantity Code refer to 1 Code 1 1 MENUITEM on PROMOTION Description DiscountPercentage UnitPrice Figure 49 JETSET MONATE ERD - Chen Notation Downloaded from hlayiso.com 62

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Step 8 - Evaluate the ERD against the source document In this last step we evaluate our ERD against the given business document (and or documents) and see if the design of our ERD and the intrinsic business rules that it encapsulates are indeed in line with what business processes or process the document present. One test is to determine whether we will be able to generate the given document from the data if the conceptual design of the DB represented in our ERD are realised in an actual physical DB. We could also revisit the business rules and from our business rules in the example provided, we are able to confidently say that the business rules are valid and feasible within the business activity from which the document originate. 2.3 Principles of good relational DB design In the previous sections of this lession we discussed the basic features of Relational tables. If a table is poorly structured, it could lead to a data management problem. This can increase an organisation's expense in two ways: ✓ 1 - In terms of data-storage, and ✓ 2 - the number of man-hours needed to work with the DB. To take care of the above-mentioned problem, a special methodology has been developed to help us change a conceptual design into a set of meaningful and useful tables. This process is known as normalisation and forms part of the DB design phase. Downloaded from hlayiso.com 63

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.3.1 Normalisation A formal definition of normalisation is the method whereby one assigns attributes to entities. This method will help us to control data redundancy in a DB and in the process eliminate data integrity Normalisation is the process of problems. evaluating table structures and reorganising them as necessary to produce a set of stable, well- In our examples in the previous sections we also attempted to design structured relations. relationsips with various keys and fields that will be able to represent a particular business driven data environment. Normal forms During the normalisation process, each table will be classified according to certain traits and design standards called normal forms. There are several normal forms developed specially to address and minimise the difficulty associated with relational tables. These forms are: ✓ First Normal Form (1NF), ✓ Second Normal Form (2NF), ✓ Third Normal Form (3NF), ✓ Fourth Normal Form (4NF), and ✓ Fifth Normal Form (5NF). The goal is to develop an end-product set of tables that are desirable to work with. The highest level of normalisation is not always desirable, most designs are easily implemented in a RDMS if designed to meet 3 rd normal form. There is a limit to the usefulness of normalisation; i.e. you have to understand what the data means in order to use normalisation successfully. A typical example is when a programmer writes a program. A compiler checks the syntax errors of the program, and although there may not be any syntax errors, the program must also be semantically correct. Normalisation should be regarded as a set of rules to aid the designer, rather than a solution to all data problems. The first three normal forms will further be discussed in detail. Non-Normalised Data In 1NF you have to decompose all data structures that are not two- dimensional, into a two-dimensional relation. All columns must contain a single data value, and all rows must have the same number of Downloaded from hlayiso.com 64

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers columns. Furthermore, any repetitive groups of the same data classification must be removed. This implies that a table is in 1NF when: ✓ each column contains the smallest meaningful value, and ✓ the table does not have any repeating groups of fields of the same classification type. Figure 50 Example of a non-normalised table First Normal Form (1NF) A table is in 1NF when: ✓ All the key attributes are defined ✓ There are no repeating groups in the table. ✓ Each attribute contains the smallest meaningful value ✓ All the attributes are depended on the primary key Steps to be taken: ✓ If the column does not contain the smallest value, it should be split into two separate fields, e.g. a first name and a surname. ✓ Eliminate the repeating groups of the same data ✓ Assign primary keys to the column values Downloaded from hlayiso.com 65

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Figure 51 Example of a table in 1NF This table stores information regarding students and the subjects that they are registered for in a specific diploma. So, a possible name for our relation would be STUDENT_REGISTRATION, and we can note it as Normalisation is the process of follows: organising data in a database. This includes creating tables and establishing relationships STUDENT_REGISTRATION (STUDNR, FIRSTNAME, SURNAME, DIPCODE, between those tables according to rules designed DIPNAME, SUBJCODE, SUBJNAME) both to protect the data and to make the database more There are a number of data irregularities that can be detected if a flexible by eliminating redundancy and inconsistent table is left in lNF. These irregularities are referred to as anomalies; i.e. a dependency. deviation from the rules that a table needs to conform to regarding Support.office.com 2017b) Relational tables. Redundant data wastes disk space and creates maintenance problems. If data The various anomalies are: that exists in more than one place must be changed, the Insert anomalies data must be changed in exactly the same way in all If a student registers for a course, but does not register for a subject, locations. (Support.office.com 2017b) certain columns will have no values and this is not allowed. Update anomalies Data about a student is captured in more than one row; i.e. a row for each different subject that the student is registered for. This makes When you normalise a updating a problem, if a value needs to be changed it has to be database, you have four goals: arranging data into logical changed in multiple rows, which can lead to data inconsistencies. groupings such that each group describes a small part of the whole; minimising the amount of duplicate data stored in a database; organising the data such that, when you modify it, Downloaded from hlayiso.com 66

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers you make the change in only Delete anomalies one place; and building a database in which you can access and manipulate the If a student decides to cancel all his subjects, it would be incorrect to data quickly and efficiently delete all his records, as this would also delete specific information like without compromising the integrity of the data in storage. his name, surname and diploma that the student enrolled for. This (Poolet, 1999) means that we'll have no record that the student ever existed, and that is incorrect. 2nd Normal Form (2NF) A table is not in 2NF if a non-key attribute is dependent on a part of the primary key. For example if the primary key is a composite value and a non-key attribute is only dependent on one part of the composite key. Looking at the example mentioned above, the primary key is a composite value; i.e. STUDNR+SUBJCODE. With the result: ✓ the student' s name and surname are determined by only part of the composite key; i.e. the STUDNR attribute. ✓ The names of the subjects that a student are registered for,are also determined by part of the composite key; i.e. the SUBJCODE. In other words,all attributes are not totally functionally dependent on the primary key. Insert anomalies An insert anomaly arises when you wish to enroll a new student. This student cannot be added to the table unless he registers for a subject as well. This is unacceptable, as a student may want to enroll for a course and stiII be unsure about the subjects he wishes to register for, leaving this decision until later. Tables like these usually have repeating values in a field due to the fact that the field does not relate to the entire primary key. Such a field that only depends on part of a primary key should be removed and placed into another table. A table is in 2NF when: ✓ If the table is in 1NF ✓ It includes no partial dependencies Downloaded from hlayiso.com 67

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Figure 52 Examples of tables in 2nd NF STUDENT (STUDNR, FIRSTNAME, SURNAME, DIPCODE, DIPNAME) SUBJECT (SUBJCODE, SUBJNAME) 3rd Normal Form (3NF) A table is not in 3NF if a non-key attribute is a fact about another non-- key attribute. In the STUDENT table above, DIPNAME is a fact about DIPCODE; i.e. a non-key field. Thus, DIPNAME is not fully functionally dependent on STUDNR, the primary key. The functional dependencies are STUDNR -> FIRSTNAME -> SURNAME -> DIPCODE -> DIPNAME. In other words, DIPNAME is dependent on STUDNR, since DIPNAME is dependent on DIPCODE and DIPCODE is inadvertently dependent on STUDNR. Certain problems are apparent. An insert anomaly exists if you try to add a new diploma to the STUDENT table. You cannot add a new diploma to the table unless you add a student. Similarly, a deletion anomaly exist when you delete a diploma, the students information is also deleted in the process. This is unacceptable. The STUDENT table must thus be split into two tables with the new table’s primary key as a foreign key as reference to the original table. Downloaded from hlayiso.com 68

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Figure 53 Examples of tables in 3rd Normal form A table is in 3NF when: ✓ If the table is in 2NF ✓ It contains no transitive dependencies. The final 3rd normal form representations of the tables could be presented as: STUDENT (STUDNR, FIRSTNAME, SURNAME, DIPCODE) DIPLOMA (DIPCODE, DIPNAME) SUBJECT (SUBJCODE, SUBJNAME) 2.4 Other data models We have learned that a data model is a represetation of the users' view of how a database is desinged in terms of the various entities and relationships. A DB model is a representation of the database on a conceptual level. After a database designer has designed a conceptual model of the database, a physical model can be implemented when the DB is created in a DBMS environment based on the model. There is, besides the relational database mode,l also other types of models. We will look at two such models next. Downloaded from hlayiso.com 69

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.4.1 Object Oriented DB modelling and design In an Object Oriented DB (OODB) the data is stored in various objects. Every object is defined in terms of its attributes and the actions or processess that can take place on the applicable object. An object database (also object-oriented database) is a database model in which Current approaches present the functionality to instatiate and information is presented in the populate objects with data from existing tables and other data form of objects as used in object-oriented programming. sources. Object databases are generally recommended when there is a Each object represents a unique instance of a data entry or entity. business need for high performance processing on complex data. Some object- oriented databases are designed to work well with Database Object 1 Object 2 object-oriented programming languages. (For-Ian, 2009). Metadata Table Object 3 Table Object 4 Table Figure 54 Objects instantiated from existing table records and data Some of the advantages of an OODB modelling and design approach are: ✓ Objects created and populated with data are easily reusable ✓ Fast access to the data ✓ Different types of data could be implemented such as images, videos and documents ✓ Objects could be embedded ✓ When an object is already defined, then it is easily to share between applications. 2.4.2 Multi-dimensional DB designs In a multi-dimensional DB data is stored in different dimensions. Where a relational table presents data in terms of rows and columns, a multi- dimensional DB could store data in more than two rows and column. A multidimensional database Data is represented in the form of a cube. (MDB) is a type of database that is optimised for data warehouse and online analytical processing (OLAP) Downloaded from hlayiso.com 70

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers Collins (2003) explains that: “In a multi-dimension database system, the applications. Multidimensional databases are frequently data is presented to the user in such a way as to represent a created using input from existing hypercube, or multi-dimensional array, where each individual data relational databases. A multidimensional database - value is contained within a cell accessible by multiple indexes” or a multidimensional database management system (MDDBMS) - implies the ability to rapidly An example of such a hypercube is presented below process the data in the database so that answers can be generated quickly. A Each cube contains number of vendors provide unique data, that is products that use relevant to its position multidimensional databases. Approaches to how data is stored and the user interface vary. (Rouse, 2006) INV004 Invoice PR0052 2017/05/10 Each of the cube dimensions acts as a table key. Products Figure 55 Example of a multidimensional data cube Some of the advantages of a multidimensional DB approach are: ✓ Fast access to the data, and query processing ✓ Quick consolidation of data into various views Downloaded from hlayiso.com 71

Introduction to Database (DB) Concepts for NCV CS/IT Students & Lecturers 2.5 Lesson Bibliography Brandenburg, L., 2017. Requirements Models and Specifications - How to Create an Entity Relationship Diagram (ERD). Retrieved May 25, 2017, from http://www.bridging-the-gap.com/erd- entity-relationship-diagram/ Chauhan, S. 2012. Difference between Primary Key and Foreign Key. Retrieved May 15, 2017, from http://www.dotnettricks.com/learn/sqlserver/difference-between-primary-key-and-foreign-key Collins, J. 2003. Comparison of Relational and Multi-Dimensional Database Structures. Retrieved June 1, 2017, from http://www.alphadevx.com/a/36-Comparison-of-Relational-and-Multi-Dimensional- Database-Structures Coronel, C., Morris, S., 2015. Database Systems: Design, Implementation, & Management 12ed For-Ian, S. (2009). Object Management DBMS Technology. Retrieved from https://www.slideshare.net/fvsandoval/03-object-dbms-technology Mukada, A., 1999. The ABC of Data Management. © Technikon Pretoria (Substantial content used as with permission from HoD) Oracle.com. 2015. Introduction to Oracle Database. (2015). Retrieved May 11, 2017, from https://docs.oracle.com/cd/E11882_01/server.112/e40540/intro.htm#CNCPT88781 Poolet, M. A., 1999, SQL by Design: Why You Need Database Normalisation | Database Performance Tuning content from SQL Server Pro. Retrieved June 1, 2017, from http://sqlmag.com/database-performance-tuning/sql-design-why-you-need-database- normalisation Rouse, M. 2006. What is multidimensional database (MDB)? - Definition from WhatIs.com. Retrieved June 1, 2017, from http://searchoracle.techtarget.com/definition/multidimensional-database Support.office.com 2017. Database design basics - Access. (n.d.). Retrieved May 13, 2017, from https://support.office.com/en-us/article/Database-design-basics-1eade2bf-e3a0-41b5-aee6- d2331f158280 Support.office.com 2017b. Description of the database normalisation basics. (2017, May 10). Retrieved from https://support.microsoft.com/en-nz/help/283878/description-of-the-database- normalisation-basics TeachICT.com. n.d. Teach-ICT AS Level ICT OCR exam board - database terminology. (n.d.). Retrieved May 15, 2017, from http://www.teach- ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/terminology/miniweb/pg14.htm Downloaded from hlayiso.com 72