Iya Abubakar Computer Centre IACC Ahmadu Bello University

Iya Abubakar Computer Centre (IACC) Ahmadu Bello University, Zaria DCS 209: Introduction to Database Management (I) Abridged Lecture Notes (Part 1) prepared and delivered by M-Auwal Gene mcpn © January, 2012

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Introduction Hello, and welcome to “DCS 209: Introduction to Databases (I)”. Over the coming weeks, we shall take time to learn about some of the general fundamental concepts of the design theories and development techniques for databases; with particular attention to relational databases. 2 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) Introduction IACC, ABU, Zaria (…continued) Concepts to be discussed shall include database models, database manipulation languages and database system implementation. Classes shall be highly interactive and you are all expected to participate actively. 3 In the Lab sessions, Microsoft Access 2007 shall be used to demonstrate the practical implementation details of concepts discussed during lectures. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) Introduction IACC, ABU, Zaria (…continued) There shall be regular assignments, bi-weekly quiz/test sessions, as well as the usual end-ofsemester written exams. 4 Before your exams though, we shall take on a simple but comprehensive database design and development project so that you will be exposed to how particular attention must always be paid to details when designing and developing database systems for clients in thedownloaded real from world. http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Requirements/Assumptions To make the most out of this course, students need to: → Have basic computer literacy: (how to start applications and use common Microsoft Office applications). → Participate regularly, fully and actively in class and in the labs. → Always complete all hands-on lab tasks and follow-up tasks faithfully. 5 → Have a willingness/readiness for independent learning. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Grading Policy The following grading policy shall apply in this course: Max. points obtainable for continuous assessments (C. A. )40% Max. points obtainable for final exams 60% 6 Out of which: C. A. will include: • Take-home assignments • Written tests (mostly over current topics) • Hands-on lab sessions (cumulative) Total Final exams will include: • Final hands-on practical (lab) exam • Final written exam (comprehensive) Total downloaded from = 05% 15% 20% 40% = 20% 40% 60% http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Outline This course is divided into four parts: Part 1: General Introduction to Databases. Part 2: Relational Database Design and Development Process. Part 3: An SQL Primer. Part 4: Course Case Study Project. 7 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction l What you shall learn – The basic concepts: l l l – Review of some database models: l l l – flat model hierarchical model network model relational model object and object-relational models The relational data model: l l l 8 Definition and explanation of terms data, database management systems relational database management systems l properties of relational tables concept of files, tables, records and fields concept of keys, entities and relations types of relationships (among entities) downloaded from NOTE: Many more introductory concepts and theories shall be discussed in class, so be there and be attentive! http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction l What is ‘data’? The term ‘data’ refers to the representation of facts, concepts, or instructions in a formalized manner suitable for communication, interpretation, or processing by humans or by automatic means. – 9 Data is any representations such as characters or analog quantities to which some particular meaning is or might be assigned. NOTE: Further detailed explanations shall be given in class, so students, be there and be attentive! downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction l What is a database? In simple terms, a database is a store of large amounts of related data, data especially in a form that can be handled efficiently by a computer. – 10 Some authors prefer to define a database as a collection of related data items, that has a regular structure, and that is organized in such a way that any desired information contained in the collection can be easily found (for viewing, editing or deleting). downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction What is a database? 11 (…continued) – When one or more computers or some other electronic devices are used to store this collection of related or structured data items and records, along with the software that manage and control how the data is accessed and used, then we have an electronic database system. – Usually, the data in a database is stored on the computer’s disk in a format which we humans cannot read or understand directly. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction What is a database? – l (…continued) To aid our access to, understanding of, and manipulation of the data stored in a database, we need a specialized software system to help out. NOTE: Although we have defined it as above, these days the word ‘database’ is commonly used in a broad sense to refer not only just to structured data but also to the DBMS that is used with it. 12 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction What is a database management system? l What is a database management system (DBMS)? A database management system (or DBMS) is a welldeveloped suite of software programs that efficiently controls how databases are created, maintained and used on one or more computers. - 13 By this, we mean that a DBMS actually helps control how data is stored, organized, retrieved and manipulated efficiently in one or more databases – thus providing an easy interface or gateway between the human operator and the data stored in a database. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction What is a database management system? 14 (…continued) – With the help of a DBMS, it is easy to retrieve information from a database, add new data, amend existing data, or delete unwanted data from a database. – Of course, a DBMS also performs other sophisticated functions, such as maintaining the overall integrity+and consistency+of data in the database, among other functions. +These terms shall be explained further in class, so be there and be attentive! downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction What is a database management system? 15 (…continued) – In the real world, examples of very successful database management systems include Microsoft Access, Microsoft SQL Server, Oracle Database, Postgre. SQL, My. SQL, and so on. – In this course, we shall focus our attention on a very popular kind of DBMS, known as the relational database management system, system or RDBMS downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Understanding RDBMS l 16 Understanding relational database management system (RDBMS) – We have seen what a database is, and we have also explained what a DBMS is. Now what is a relational database management system, or RDBMS? – In the early days of computer development, there were database systems which mostly conformed either to the hierarchical data model or the network data model downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Understanding RDBMS 17 (…continued) – While they worked well, a major drawback was that these models were too technical for ordinary computer operators to use. – In 1970 or thereabouts, a research fellow called E. F. Codd who was working at IBM introduced the relational data model In this model, the database is represented as a collection of tables which are related to one another through some sort of keys We shall talk more about relational databases later, after reviewing some other interesting data models first. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of some data models l 18 Review of some database models – A database model is a theory or concept or specification that describes how a database is structured and used. – Several basic types of database models have been developed over the years – including flat, hierarchical, network and relational. – Such models describe not only the structure of the conforming databases but also the operations that can be performed on them. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of some data models 19 (…continued) – Typically, a database has a schema, schema which is a description of the model, including the types of entities that are in it and the relationships among them. – We shall look at a few database models here and later, we shall discuss the fundamental elements of relational databases and how to develop them. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the flat model l The flat model – The flat (or table) model consists of a single, twodimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another. – 20 However, a flat file structure involves data records that really have no structured interrelationship. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the flat model – – 21 (…continued) A flat file might take up less computer space than a structured file, but requires the database application to know exactly how the data are organized within the file otherwise the application will not work. Take a simple tabular file that is used to store a list of Local Government Areas with information about each – such as name, the State, Country and Region it belongs to, population, land area, etc: Pretty Picture downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the flat model (…continued) Fig. 1: Flat file structure. 22 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the flat model 23 (…continued) – In such a tabular flat file as shown in Fig. 2, LGA names and other details are stored such that each row would have the specific data values associated with an individual LGA. – Columns of the table often have a type associated with them, defining them as character data, integers or floating point numbers, date or time information, etc. – Strictly speaking, a flat file model may not qualify as a data model, but is quite common for people to loosely consider it as one. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the hierarchical data model l The hierarchical model – A hierarchical data model is a data model in which the data is organized such that records are linked together into a tree-like structure. – 24 The structure allows repeating information using parent-child relationships whereby any child may only have one parent but a parent can have multiple children. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the hierarchical data model 25 (…continued) – Parents and children are tied together by links called “pointers”. A parent will have a list of pointers to each of their children. This type of database is highly inflexible but can be very efficient in the processing of data. – The hierarchical model collects all the instances of a specific record together as a record type. These record types are the equivalent of tables in the relational model, and with the individual records being the equivalent of rows. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the hierarchical data model – (…continued) The hierarchical model uses 1: N mapping between record types to create links among records. Fig. 2: Hierarchical structure. 26 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the hierarchical data model 27 (…continued) – Hierarchical structures were widely used in the first mainframe database management systems; and even now they are used in describing the structure of XML documents. – This structure allows one 1: N relationship between two types of data but, they are quite restricted. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the hierarchical data model 28 (…continued) – For example, the hierarchical structure is inefficient for certain database operations when a full path (as opposed to upward link and sort field) is not also included for each record. – Despite the restrictions however, this structure is very efficient to describe many relationships in the real world; such as recipes, table of contents, ordering of paragraphs/verses, any nested and sorted information. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model l The network model – The popularity of the network data model coincided with the popularity of the hierarchical data model. – 29 Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model 30 (…continued) – In many ways, the network database model was designed to solve some of the more serious problems with the hierarchical model. – Specifically, the network model solves the problem of data redundancy by representing relationships in terms of records and sets rather than hierarchy. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model 31 (…continued) – Network databases were designed to address some of the issues of inflexibility of the earlier hierarchical databases. – Each parent could have many children and each child could have many parents. – This model gives more flexibility of data access than the hierarchical model but at the cost of processing efficiency. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model 32 (…continued) – According to the definition given by the CODASYL (Conference on Data Systems Languages) specification, the network model organizes data using two fundamental constructs, called records and sets – Records contain fields (which may be organized hierarchically, as in the programming language COBOL). downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model (…continued) – Sets (not to be confused with mathematical sets) define one-to-many relationships between records: one owner, many members. – A record may be an owner in any number of sets, and a member in any number of sets. Pretty Picture 33 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model (…continued) Fig. 3: Network structure. 34 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model 35 (…continued) – The network model is a variation on the hierarchical model, to the extent that it is built on the concept of multiple branches (lower-level structures) emanating from one or more nodes (higher-level structures), while the model differs from the hierarchical model in that branches can be connected to multiple nodes. – The network model is able to represent redundancy in data more efficiently than in the hierarchical model. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review the network model 36 (…continued) – The operations of the network model are navigational in style: a program maintains a current position, and navigates from one record to another by following the relationships in which the record participates. – Records can also be located by supplying key values. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the relational model l The relational model – The theory of relational databases is based on the idea that information systems should maintain data independence, independence i. e. that the user of a database system should not be concerned with the internal workings, or structure of data in the system. – 37 A relational database is a way of organizing data such that the data appears to the user to be stored in a series of interrelated tables downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the relational model 38 (…continued) – In 1970 or thereabouts, a research fellow called Edgar F. Codd who was working at IBM introduced the relational data model. In this model, the database is represented as a collection tables which are related to one another through some sort of keys – Edgar Codd proposed that users should be abstracted from the internal representation of the data, such that if the internal representation of that data were to change (e. g. because of system growth), the way that the user perceives the data should remain unchanged. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the relational model 39 (…continued) – To this end, Codd proposed a “relational view” of data, rather than pre-existing graphical or network views. – Interest in this model was initially confined to academia, perhaps because theoretical basis is not easy to understand. Pretty Picture downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the relational model (…continued) Fig. 4: Relational structure. 40 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of the relational model – 41 (…continued) Relational databases did not become mainstream until around 1980 but from then onward however, they became the dominant type for high-performance applications because of their efficiency, ease of use, and ability to perform a variety of useful tasks that had not been originally envisioned. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of object and object-oriented models l 42 Object and object-relational models – Object-oriented databases became a new focus of research during the 1990 s, in part because of the great success that the object-oriented concept was having in programming languages (e. g. , C++ and Java). – Such databases have had some success in fields in which it is necessary to accommodate bulky and more complex data than relational systems can easily cope with, such as multimedia and engineering data, data and some object-oriented concepts were thus integrated into leading commercial relational database products. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of object and object-oriented models – – 43 (…continued) Object-relational database management systems (ORDBMSs) add new object storage capabilities to the relational systems at the core of modern information systems. As mentioned above, these new facilities integrate management of traditional fielded data, complex objects such as time-series and geospatial data and diverse binary media such as audio, video, images, and applets. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of object and object-oriented models 44 (…continued) – By encapsulating methods with data structures, an ORDBMS server can execute complex analytical and data manipulation operations to search and transform multimedia and other complex objects. – It must be noted, however, that object databases have not done pretty well because of a lack of standardization: although standards were defined by the Object Database Management Group (ODMG), they were never implemented well enough to ensure interoperability between products. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction Review of object and object-oriented models – 45 (…continued) Nevertheless, object databases have been used successfully in many applications: usually specialized applications such as engineering databases or molecular biology databases rather than mainstream commercial data processing. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational data model l What you shall learn – The relational data model: l l l 46 l properties of relational tables concept of files, tables, records and fields concept of keys, entities and relations types of relations (among entities) downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l 47 Properties of Relational Tables – We have already learnt that a relational database allows the definition of data structures, storage and retrieval operations as well as integrity constraints. – We have also learnt that in such a database the data and relations between them are organized in tables. – A table is a collection of records and each record in a table contains the same fields. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model – (… continued) As tables, relational databases have some important properties as enumerated below: Values are atomic o Each row is unique o Each column has a unique name o Column values are of the same kind o The sequence of columns is insignificant o The sequence of rows is insignificant o 48 downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l 49 (… continued) Understanding files, tables, records and fields – In data processing, using an office metaphor, a file is simply a collection of related records. Database files consist of tables, queries, forms, views, reports and so on which are related to the subject of the database. – A table in a relational database is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. – A table has a specified or fixed number of columns, but can have any number of rows. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model – 50 (… continued) Each row in a table represents a record; while the vertical columns constitute the fields of the record. Pretty Picture downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model (… continued) each column is a field or attribute of a record Date Item. Code Supplier Unit. Price Qty. Received TOTAL 24/05/2010 TB-720 EMENCO pvt LTD, INDIA 620. 00 75 46, 500. 00 24/05/2010 TB-382 EMENCO pvt LTD, INDIA 480. 00 30 14, 400. 00 24/05/2010 WH-555 GUING LU INT’L, CHINA 1, 800. 00 20 36, 000. 00 : : : each row is a record of one stock entry and all the records together make up a table 51 downloaded from Fig. 5: Concepts of fields and records explained. http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 52 (… continued) – As it is, the single table shown in Fig. 5 with a row-and-column structure does not, technically speaking, constitute a relational database. A relational database must have more than one table with interrelated data, data not just one table. – The stocks entry table we have seen in Fig. 5 is, technically speaking, a flat file type of database. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 53 (… continued) – But if we now bring in, say, the daily sales record table, and consider it together with the stocks entry table shown in Fig. 5, then we are getting closer to having a relational database. – Take note, however, that the idea of having just two or more tables together does not necessarily make a relational database. The two or more tables must be related via at least one key, before we can say we are having a relational database. So, what is a “key”? “key” downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l Understanding keys, entities 54 (… continued) and relations – In a relational database, certain fields of a table may be designated as “keys”. – [And] as you already know by now, databases use tables to organize information; and each table consists of a number of rows, each of which corresponds to a single database record. – So, how do databases keep all of these records straight? It’s through the use of keys. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 55 (… continued) – Simply put, a key (or record key) key is just any data field (or attribute) that can be used to identify a record in a table. This means that any of the data fields in a table can be used as a record key. – However, we often don’t just want to identify records, but we want to uniquely identify records. So rather than talk about just keys, we often talk about primary keys. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 56 (… continued) – A primary key is an attribute that uniquely identifies any given record in a table. – It can either be a normal attribute that is guaranteed to be unique (such as a student’s Matriculation Number, or a bank customer’s Account Number in a table with no more than one record person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server or an auto-number column in Microsoft Access). downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l 57 (… continued) Entities – An entity is a person, place, thing, event, or concept of interest to the business or organization about which we want to collect and store multiple instances of data. – For example, in a school environment possible entities might be Student, Instructor, and Class. – And in a banking environment, possible entities might include Customer, Account, Teller, Auditor or Cash Withdrawal. NOTE: Further detailed explanations shall be given in class, so students, be there and be attentive! downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l 58 (… continued) Entities – An entity must have a name, which is a noun; and has attributes which describe the data we are interested in storing. – An entity should also have an identifier, which uniquely identifies one instance of that entity. – The attribute which acts as the identifier is usually underlined or marked with an asterisk. NOTE: Further detailed explanations shall be given in class, so students, be there and be attentive! downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model l 59 (… continued) Types of relationships (among entities) – You will recall that a relational database is a way of organizing data such that the data appears to the user to be stored in a series of interrelated tables – In database speak, the relationship among entities is simply the interaction between the entities; and such interactions are usually derived from the “business rules” that operate on the domain of discourse. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 60 (… continued) – Thus in a commerce-oriented database system for example, a customer places one or more orders (for one or more items to be supplied). – So the word “places” defines the relationship between that instance of a customer and the order or orders that they place. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 61 (… continued) – Depending on the notation used, a relationship may be represented by a diamond shape, or more simply, by the line connecting the entities. – In either case, verbs are used to label the relationships. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 62 (… continued) – A major part of designing a relational database is dividing the data elements into related tables. – After all else is done, you will be ready to start working with the data, and both you and the DBMS will rely on relationships between the tables to pull the data together in meaningful ways. – Let’s take a quick and relatively simple example: downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 63 (… continued) – In a commerce-oriented system, information about orders (placed by customers) customers is useless unless you (and/or the DBMS) can tell which customer placed a particular order. A major part of designing a relational database is dividing the data elements into related tables. – However, good database design principles require that you don't store customer and order information in the same table. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 64 (… continued) – Instead, you should store order and customer data in two different tables that will then be related in one of three or four ways (that we shall discuss shortly). – To view any order and its corresponding customer information at the same time, you (and/or the DBMS) can then use the relationship between the two tables to do so. – This approach is called “normalization” (we shall look at that later in this course); and the tables are said to be “normalized” downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 65 (… continued) – Normalized tables are the foundation of relational databases, and relationships are the cornerstone. – Okay, example done, now let’s go back to our discussion about types of relationships among entities. – So we know what relationships are: you have a lot of relationships in your personal life, starting with members of your family, for example. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model 66 (… continued) – For instance, you and your mother are related. You have only one mother, but she may have several children. – You and your siblings are related, too; and if you have many brothers and sisters, then they will have many brothers and sisters as well. – If you have a spouse, then both you and your spouse have a spouse -- you each have each other -- but only one at a time. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model – Database relationships are somewhat like that too, in the sense that they are associations between entities (or tables). – In relational database studies, there are four major types of relationships that should be understood by everyone. These are: l l l 67 (… continued) l one-to-one relationships (1: 1) one-to-many relationships (1: n) many-to-one relationships (n: 1) many-to-many relationships (m: n) downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model (… continued) One-to-One Relationships (1: 1) – Here, both entities can have only one record on either side of the relationship. In a workplace for example, each employee is associated with one department (assuming no employee is allowed to serve in more than one department at a time). – 68 In the case of families, 1: 1 relationship can be seen in settings where only monogamy is allowed: at any given time, a man can have one wife and a wife can have only one husband. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model (… continued) One-to-Many Relationships (1: N) – Here, one entity is associated with many other entities. The primary key table contains only one record that may relate to none, or many records in the related table. 69 – Example: A company may have many employees, all of whom belong to that one company. – Example: You (and your siblings) have only one mother, but your mother may have several children. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model (… continued) Many-to-One Relationships (N: 1) – Here, many entities are associated with only one entity. This is actually like the one-to-many relationship in reverse. 70 – Example: In a company, many employees could be associated with one project. – Example: In a class of a school, many students may be associated with (taught by) one instructor. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model (… continued) Many-to-Many Relationships (M: N) – Here, many entities are associated with many other entities. 71 – Example: In a company, many employees could be associated with multiple projects (completed/existing), and at the same time, projects are associated with multiple employees. – Example: If you have several siblings, so do your siblings (have many siblings). downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria Part 1: General Introduction A closer look at the relational model – 72 (… continued) During actual implementation, many-to-many relationships require a third table, known as an “associate”, “linking” or “junction” table, because relational systems can’t directly accommodate the relationship. downloaded from http: //www. auwalgene. com/mystudents

DCS 209: Introduction to Database Management (I) IACC, ABU, Zaria The End Thank You Coming up in Part 2: relational database design and development process, with emphasis on E-R diagramming and data dictionaries 73 downloaded from http: //www. auwalgene. com/mystudents