Design of a Database Management System in the …



DATABASE MANAGEMENTDesign of a Database Management System in the Development of Computer Applications 1. Explain the concept of a databaseQUESTION!What:stores data, manages data, Extracts or retrieves information using that data??Answer: A?DATABASEA?database?is an organized collection of data. This data is?organized so that information can be retrieved from it. This data can be stored in any number of formats such as index cards, printed on paper, electronically stored on a computer.These databases below are called traditional databases where you manually sort and count and extract information.?Index CardsTelephone DirectoryStores data by?subject?or topicStores data in groups?(and even by colour)categorized by Business,?or government for example?(White pages, yellow pages)Each business or subscriber is?uniquely identified by a?telephone number?Example:Some data on one index card?can be found on other index cards.?For example, one set of cards may?store the customers' name,?address, and telephone number.Another set of cards may store all of?the?products for sale.?Yet another set of cards may store?the set of orders for those products.??Example:Some businesses in the yellow?pages can also be found in the white?pages. You can count how many?listed subscribers have a specific?surname, or how many have the?same area code (although it may?take a while to count them!)?Identifying one record from theother:?Each customer will have a?customer?number.Each product will?have a product?number that will be identify that?product and?how many are in stock.Each order will contain an?order?number, the?customer number for?the?customer who ordered the?product, and the quantity ordered.?Identifying one record from theother:Each subscriber will have a?telephone number. If a subscriber?has more than one telephone number?each number is linked to that one?subscriberAs you add more data, more index cards are added and you would spend time searching for your information.This can become time-consuming!!As you add more data, more pages are added to the directory,?and you would spend time searching for your information.This can become time-consuming!!?We now have various types of software programs to manage the data in a database.??That is, we can use that software program to define construct and manipulate the database.?One type of software is a?Database Management System (DBMS). Microsoft Access is an example of a DBMS.?What is DBMS?A DBMS is a collection of programs that helps the user to create and maintain a database.2. Use terminology commonly associated with a database?Some Terminology?For us to understand the terminology that will help us manage a database, let us introduce the following sets of data.The following set of data can be stored in a set of 12 index cards. However, if we store them using a Database Management System such as Microsoft Access, then this set of data is stored in a file also called a?TABLE.We can name the table below as PRODUCT to show that the data stored in the table is about different products.Now, the data we have stored about the products are all stored in a horizontal?ROW.?So, product Number 1 identifies the Chai, which was ordered from a supplier whose ID is 1. This Chai is part of a category, category 1 in this example. The Chai is sold in a set 10 boxes which each contain 20 bags. This is sold for $18.00 and there are 39 of them is stock.TABLE:?PRODUCTProductIDProduct NameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStock1Chai1110 boxes x 20 bags$18.00392Chang1124 - 12 oz bottles$19.00173Aniseed Syrup1212 - 550 ml bottles$10.00134Chef Anton's Cajun Seasoning2248 - 6 oz jars$22.00535Chef Anton's Gumbo Mix2236 boxes$21.3506Grandma's Boysenberry Spread3212 - 8 oz jars$25.001207Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.$30.00158Northwoods Cranberry Sauce3212 - 12 oz jars$40.0069Mishi Kobe Niku4618 - 500 g pkgs.$97.002910Ikura4812 - 200 ml jars$31.003111Queso Cabrales541 kg pkg.$21.002212Queso Manchego La Pastora5410 - 500 g pkgs.$38.0086?A?KEY?is used to specifically identify a ROW. That means a key, like any key, fits a lock only made for it.?Now have a look at the numbers in the?ProductID?column. Each number is different, just as each product name in the?ProductName?column is different. These are called?Candidate Keys. They can both identify a specific product in the table.You should note however that the data in these two columns are unique (different). ProductID and ProductName are called?Keys?since they are unique.?So ProductID 1 uniquely describes Chai while ProductID10 uniquely describes Ikura.?Notice that ProductID does not have a number that is repeated in the list. So with any ProductID, there is no guessing what product information is described for that ProductID.This makes PRODUCTID a?PRIMARY KEY. ?In our example,?ProductName?is also called an?Alternate Key,?another key that can also be used as a Primary Key!?ProductName?can?ALSO?be called a?SECONDARY KEY.?It may not be unique for each row, but we can still use it to find information from the table, and also to sort the data in the table.?We don't know from the table, but there are many products called Chai, or Aniseed Syrup. while only ONE ProductID can identify only a unique or specific product Name.Sometimes there are no columns that contain unique values. When this happens, we then look to see if data in two columns can provide a unique key to the other items in the table.Let us consider the following table called ORDER:?TABLE:?ORDERSupplierIDProductIDQuantityOrderedDateOrderedShipped1215012/12/2012Yes1415011/12/2012No525512/12/2012No?Here, we have a table the keeps a log of how many products were ordered and when. We have ProductIDs 2 and 4 ordered from the same Supplier 1. In this case:SupplierID cannot be a Primary Key since SupplierID 1 is shown twice in the column.ProductID cannot be a Primary Key since ProductID 2 is shown twice in the column.So!Let us look at joining SupplierID and ProductID.That is,SupplierID 1 and ProductID 2 in the first row are unique.?So are;SupplierID 1 and ProductID 4,andSupplierID 5 and ProductID 2.When two fields together form a (unique) Primary key, they are called a Composite Key. If by chance we had three fields that together formed a Primary Key, then they too are called a Composite Key! So, once you have more than one field that becomes a primary key, then it is also called a Composite Key.Let us recap:A field with unique data - can be a Primary KeyOther fields also have unique data - you have Alternate Keys (So your school identification Card, and your HospitalID and your ticket to a show are all Alternate keys are they all mean YOU)More than one field that together provides unique data - Composite Key?Now! Let us add some more informationSuppose you have this table below. The name of the table is?SUPPLIER.?TABLE: SUPPLIERSupplierIDCompanyNameContactNameContactTitleAddress1Exotic LiquidsCharlotte CooperPurchasing Manager49 Gilbert St.2New Orleans Cajun DelightsShelley BurkeOrder AdministratorP.O. Box 789343Grandma Kelly's HomesteadRegina MurphySales Representative707 Oxford Rd.4Tokyo TradersYoshi NagasePurchasing?Manager9-8 Sekimai?Here, we have:SupplierID as the Primary KeyCompanyName is an Alternate Key and a Secondary KeyContactName can be an Alternate key or Secondary Key, BUT we would not want to use it as people at the company can change (promotion or change jobs), while a SupplierID number will ALWAYS refer to that specific Company Name?Take a look at the Product Table above. Did you notice that SupplierID was also in that table?If a Primary Key in one table is found in ANOTHER Table, then that Primary key is called a?FOREIGN KEY.?Here we go...In?the Product Table,?ProductID?is the Primary Key, and?SupplierID is the?Foreign KeyIn the Supplier Table,?SupplierID?is the Primary Key?3. Distinguish among terminology associated with files and databases The following set of data can be stored in a set of 12 index cards. However, if we store them using a Database Management System such as Microsoft Access, then this set of data is stored in a file or a?TABLE.We can name the table below as PRODUCT to show that the data stored in the table is about different products.?TABLE:?PRODUCT???????This is a?COLUMN↓?????????ProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStock?1Chai1110 boxes x 20 bags$18.0039?2Chang1124 - 12 oz bottles$19.0017?3Aniseed Syrup1212 - 550 ml bottles$10.0013?4Chef Anton's Cajun Seasoning2248 - 6 oz jars$22.0053?5Chef Anton's Gumbo Mix2236 boxes$21.350?6Grandma's berry Spread3212 - 8 oz jars$25.00120←This is a ROW7Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.$30.0015?8Northwoods Cranberry Sauce3212 - 12 oz jars$40.006?9Mishi Kobe Niku4618 - 500 g pkgs.$97.0029?10Ikura4812 - 200 ml jars$31.0031?11Queso Cabrales541 kg pkg.$21.0022?12Queso Manchego La Pastora5410 - 500 g pkgs.$38.0086??????????Now, the data we have stored about the products are in horizontal?ROWs.?So, product Number 1, identifies the Chai, which was ordered from a supplier whose ID is 1. This Chai is part of a category, category 1 in this example. The Chai is sold in a set 10 boxes which each contain 20 bags. This is sold for $18.00 and there are 39 of them is stock. Microsoft Access refers to a ROW as a?RECORD. Another term for a row is a ?TUPLE.All of the ProductIDs (from 1 to 10) are in a?COLUMN.?Microsoft Access refers to a COLUMN as a?FIELD. Another term for a field is an attribute. So, the ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, and UnitsInStock are all fields.??How many fields and records are in the table below?TABLE: SUPPLIERSupplierIDCompanyNameContactNameContactTitleAddress1Exotic LiquidsCharlotte CooperPurchasing Manager49 Gilbert St.2New Orleans Cajun DelightsShelley BurkeOrder AdministratorP.O. Box 789343Grandma Kelly's HomesteadRegina MurphySales Representative707 Oxford Rd.4Tokyo TradersYoshi NagasePurchasing?Manager9-8 Sekimai?The Supplier Table has five fields and four records.?Data TypesEach field contains different types of data. For example, in the Supplier table above, the SupplierID contains numbers, while the Company Name, Contact Name, and Contact Title all contain text. The Address field contains Alphanumeric data which means that each field can contain both text and numbers.?Consider the Order table below:TABLE:?ORDER?SupplierIDProductIDQuantityOrderedDateOrderedShipped1215012/12/2012Yes1415011/12/2012No525512/12/2012No?In this table we have five fields and three rows.?SupplierID, ProductID, and QuantityOrdered are all numeric fields.DateOrdered is a date field.Shipped is a logical field with two options only, Yes or No.?Recap:Data typesAlphanumeric - both text and numbersNumeric - ?numbersDate - datesLogical -Option for Yes or No, True or False4. Outline the advantages and limitations of databases Advantages of using a DBMSData redundancy is reduced. That is, all fields and data must be useful and necessary. If it is not needed then it should not be captured in the database.Errors created in updating data are reduced so that there is increased consistency in the data that is captured.Greater data integrity (You can trust the data) and independence from applications programs (meaning the data can be imported or exported to other applications)More users can have access to the data as required?through multiple user interfaces.Improved data security since users could use passwords to access certain aspects of the database.?Reduced data entry since users would not have to re-enter data that is already stored in the database.Reduced storage (no more stacks of filing cabinets.Data can be?backed-up and recovered?as necessaryThe database can be set up so that?vital information MUST be entered?and cannot be omitted or left blankHowever, the following can be viewed as some of the limitations of a database:DisadvantagesTo create a GOOD database system, its design can become complex, difficult, and even time-consuming in order to get all of the fields, data and tables properly organized and entered.Substantial hardware (computers with appropriate hard drives) and purchasing of the required software can be expensiveDamage to database by power outages, hard drive failure, or deleting data either by error or deliberately affects virtually all applications programsMoving form a traditional-based system (like index cards) to a database system can be time-consuming and costlyAll users of the DBMS should have some initial training5. Create a database When you create a database:You need to know the purpose of your database!What data will you use? This will help with your field names (title, firstname, lastname, address, companyname, age, and so on).You group meaningful data together so that your table will have a meaningful name, such as STUDENT, or SUBJECT.How will your tables be linked together? You need a primary key in one table to link with a foreign key in another table.What data types will your data have (alphanumeric?, numeric?, date?, logical?)?It is best to write these out before you attempt to create your database using the Database Management software.6. Modify a table structure Modifying a Table StructureFields can be?added to, modified, or deleted directly from a?database table structure.The illustrations below use Microsoft Access 2007. In order to modify your table structure, table should be in DESIGN view. You can check this by looking at the icon just below the Office Button, named View. The illustrations are currently in DESIGN view, with the icon showing the option for you to switch to DATA view.Adding Fields to a Database TableTo add a field, click on the cell to the left of the Field Name to highlight where you want to insert your row.?Then, Click on?Insert Rows, to add a row where you have selected.?Deleting Fields from a Database TableSimilar to adding a row, you select the row that you want to delete and then click the?Delete Rows?icon to remove the row.?Data types can be modifiedYou can modify the type of data that you want to capture in a data field.In this illustration, QuantityPerUnit was initially added as a text field. It is later modified to contain numbers.?Note that when you modify data types, if there is data is the records already, you may lose it if it does not match the new data type. For example, if 'dozen' was text entered in a record for QuantityPerUnit, and the data type was modified to Number, the text will be deleted since it is not an integer.??You can also format the?Data Type?in the?Design View:In?Design View, click the field name you wish to define or create a new fieldClick the?Data TypeChoose the appropriate?Data Type and format the field in the?Field Properties Dialog box:??? Deleting a TableRight click on the table you want to delete and choose?Delete?from the drop down menuRenaming a TableRight click on the? table you wish to rename and choose?RenameType in the new name???7. Sort a database Sorting your data?You can watch this short video on sorting data in your database table. This example illustrates how data is sorted based on a single field containing text or a single field containing numbers. You can sort in ascending order (A to Z) or descending order (Z to A).To Sort using Text ValuesClick the drop down arrow at the top of the field you wish to sort.?When the menu appears, select either Sort A to Z?or the?Z to A.?Notice in the result below, the data is sorted by TourName?only, the other two fields PartyNo and ResDate are not sorted??To sort by more than one field, you select both columns and then select the Sort option from the ribbon:?Notice the result of the sort. The data is sorted by TourName and then by PartyNo. That is,?PartyNo is sorted?for all of the Boat Tours, then PartyNo is sorted for all of the Cemetry Walk 1 data.?8. Establish relationshipsCreating RelationshipsBefore we rush to create our relationships in our Database Management application, such as Microsoft Access, we need to understand what fields in each table we are linking.Using our PRODUCT and SUPPLIER tables again, remember that we had Primary Keys and Foreign Keys.TABLE: SUPPLIERPrimary Key: SupplierIDSupplierIDCompanyNameContactNameContactTitleAddress1Exotic LiquidsCharlotte CooperPurchasing Manager49 Gilbert St.2New Orleans Cajun DelightsShelley BurkeOrder AdministratorP.O. Box 789343Grandma Kelly's HomesteadRegina MurphySales Representative707 Oxford Rd.4Tokyo TradersYoshi NagasePurchasing?Manager9-8 SekimaiProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStock1Chai1110 boxes x 20 bags$18.00392Chang1124 - 12 oz bottles$19.00173Aniseed Syrup1212 - 550 ml bottles$10.00134Chef Anton's Cajun Seasoning2248 - 6 oz jars$22.00535Chef Anton's Gumbo Mix2236 boxes$21.3506Grandma's Boysenberry Spread3212 - 8 oz jars$25.001207Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.$30.00158Northwoods Cranberry Sauce3212 - 12 oz jars$40.0069Mishi Kobe Niku4618 - 500 g pkgs.$97.002910Ikura4812 - 200 ml jars$31.003111Queso Cabrales541 kg pkg.$21.002212Queso Manchego La Pastora5410 - 500 g pkgs.$38.0086?TABLE:?PRODUCTPrimary Key: ProductIDForeign Key: SupplierID?TABLE: ORDERSPrimary Key: SupplierID + ProductIDSupplierIDProductIDQuantityOrderedDateOrdered1215012/12/20121415011/12/2012425512/12/2012?Creating Relationships using Microsoft AccessNow that we know the Primary keys and Foreign Keys, in each table, we can create a relationship between them using Microsoft Access.BEFORE you create any queries or reports in your Database Management application, you should create a relationship between your tables.To do so, select Relationship from the (Database) Tools?menu.You should see a grey area (Access 2003) or a blue area (Access 2007).?The Show Table dialog box like the one below should be visible.??If this window does not appear:Access 2003: Right-click?in the?gray?area and select the SHOW WINDOW or SHOW TABLE optionAccess 2007: You may need to click the SHOW TABLE icon, located just below the External Data name (see the diagram below)?You select the first table, click ADD, then select the next table and also click ADD. You add each table and then click Close.Now, we need to link the tables together. Look at the fields in each table. You should note that there is a common field name?in two tables. This is the field we will use. Select this field in one table and while holding down you left mouse button, DRAG it to the next fieldname?in the next table, don’t worry if you see a ‘no entry’ sign appear as you drag! Once you are reached the next field name, release the mouse button and another window like the one below should appear:??In this window, you need to ‘Enforce Referential Integrity’. Then click Create. The relationship type illustrated in this window tells you that there is One Supplier ‘supplying Many Products.Notice that a line is drawn from Supplier (with a 1 next to it) to Product (with an infinity sign next to it), even though you could have dragged from Product to Supplier. Microsoft Access determined the type of relationship for us as one-to-many.Close this window (you will be prompted to save the relationship).9. Query a database using multiple search conditionsTo create a query:Access 2003: Click on Queries on the left pane, and choose ‘Create Query in Design View’. Add both tables, and close the Show Table window.Access 2007: Click Insert, from the ribbon, and choose Query Design (near to the right hand side of the ribbon)You should see a screen similar to the one below:??Next, you select and add the tables that you wish to use in the query. In this example, we've selected the Product table and clicked on the Add button. When you are finished selecting the tables, click on the Close button.A query may sometimes be formed like a question. For example 'How many Units are there remaining for the Chai product? This suggests that you should use the UnitsInStock field and the Product field. Sometimes you may want to include another field that will help in the query, like the name of the company that sells the Chai.You select these fields from the tables you have added to the query.?For each one double click on the field, and it will show in the pane below:???To see your result, you click on the icon located in the top left hand corner of the screen. This will show you the result of the query you created, such as the one below for the query we just?created???To return to your query screen, look in the same location at the top left hand side of the screen. The icon has changed to look like this?. Clicking on this icon takes you back to Design View.??So, it is like a switch:?to show your results???to enter your criteria??Multiple Search Options?Sometimes getting your result from a query means that you have to ask one question or more than one question. Let us look at some examples:Example 1:?Suppose you want a list of the number of copies of The Avengers or Batman are in stock.?TitleIDTitle?Number in StockCost?3D45The Avengers42?24.99?2S23?Batman12?16.99?1H67?Superman16?29.99?6J90?Thor19?24.99?4M98?Spiderman21?14.99?The rows that contain the titles 'The Avengers' and 'Batman' are selected.Here, there are two rows or two records that meet the criteria. These records are highlighted in blue. The fields TITLE and NUMBER IN STOCK are the important ones to answer the query question.?TitleIDTitle?Number in StockCost?3D45The Avengers42?24.99?2S23?Batman12?16.99?1H67?Superman16?29.99?6J90?Thor19?24.99?4M98?Spiderman21?14.99?Example 2:?You may want to find out how many boys are in Forms 4 and 5.For Example 2, this means that you need to search the database table for the field that contains the Form number, and the other field that contains the gender of the student. Suppose the following table with three fields contained five records:FormSubjectsGender36M47M46F57F35MTo answer the question, '?how many boys are in Forms 4 and 5?', the FORM and GENDER Fields would be needed.?We then need to extract those in Forms 4 and 5, and then the Boys or in this case, the Males, denoted by M.?FormSubjects??Gender?Form?SubjectsGender??Form?SubjectsGender??36?M??3?6M??36?M??4?7?M?4?7M??4?7?M?4?6?F?4?6F??4?6?F?5?7?F?5?7F??5?7?F?3?5?M?3?5M??3?5?M???FORM = 4 AND FORM = 5SELECTS THE ABOVE ROWS OF DATA?GENDER = 'M'SELECTS ALL?MALES????THE ROW(S) OF DATA THAT ARE IN COMMON WITH:FORM = 4 AND FORM = 5 AND GENDER = 'M'WILL BE YOUR RESULT (1 Record)?Now let us apply these concepts to an example in Microsoft Access. Consider a database called HOSPITAL with two tables, DOCTOR and WARD?TABLE: DOCTOR?TABLE: WARD?Calculated fieldsNew fields can be created by performing calculations. The new field name and its calculation is entered on the first row as a field. THe diagram below illustrates where a calculation is entered.The format for creating a calculation is as follows:NEW FIELD NAME:[each field name used must be enclosed in SQUARE BRACKETS]Note that the new field name is to the left of the colon: and the calculation is to the right of the colon.Examples: Cost:[Price]+[Markup] where [Cost] and [Markup] are fields in a tableIncrease:[Price]*10% where [Price] is a field in a table and the price is multiplied by 10% to obtain 10% of the price.??Query Question: Each doctor must deduct 10% of his or her salary for a special tax. Calculate how much each doctor must pay.?Query Design ViewQuery Data View (Result?)???10. Determine the results of a search on a database given multiple conditions Creating queries using relational and logical operatorsCreating queries using relational and logical operatorsOnce you have entered data in your tables then you can ask questions and get information from your data.?Relational OperatorsOne way to get information from the data stored in your tables, is through relational operators, as shown in the table below:?Operator?Symbol?Greater than>Greater than or Equal to>=?Less than<?Less than or equal to?<=Equal to?=Not Equal to<>?Let us extract some information from the PRODUCT table below using each of the relational operators.?TABLE:?PRODUCTProductIDProductNameSupplierIDCategoryIDQuantityPerUnitUnitPriceUnitsInStock1Chai1110 boxes x 20 bags$18.00392Chang1124 - 12 oz bottles$19.00173Aniseed Syrup1212 - 550 ml bottles$10.00134Chef Anton's Cajun Seasoning2248 - 6 oz jars$22.00535Chef Anton's Gumbo Mix2236 boxes$21.3506Grandma's Boysenberry Spread3212 - 8 oz jars$25.001207Uncle Bob's Organic Dried Pears3712 - 1 lb pkgs.$30.00158Northwoods Cranberry Sauce3212 - 12 oz jars$40.0069Mishi Kobe Niku4618 - 500 g pkgs.$97.002910Ikura4812 - 200 ml jars$31.003111Queso Cabrales541 kg pkg.$21.002212Queso Manchego La Pastora5410 - 500 g pkgs.$38.0086?Question 1What are the products that cost $50.00?We need to use the ProductName and UnitPrice fields to answer this question.However, since we need those products that cost $50, we use the criteria:UnitPrice = 50to extract those products that cost $50Note that we do not include the Dollar sign ($) in the criteria.The diagram below illustrates how we enter the criteria for this query.??From our PRODUCT Table above, since there are no Products that cost $50, there should be no records output.?? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download