3.6

|
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
 3 views
of 31

Please download to get full document.

View again

Description
1. Chapter 3.6 Databases3.6 (a) Files and DatabasesOriginally all data were held in files. A typical file would consist of a large numberof records each of which would…
Share
Transcript
  • 1. Chapter 3.6 Databases3.6 (a) Files and DatabasesOriginally all data were held in files. A typical file would consist of a large numberof records each of which would consist of a number of fields. Each field would haveits own data type and hold a single item of data. Typically a stock file would containrecords describing stock. Each record may consist of the following fields. Field Name Data Type Description String Cost Price Currency Selling Price Currency Number in Stock Integer Reorder Level Integer Supplier Name String Supplier Address StringThis led to very large files that were difficult to process. Suppose we want to knowwhich items need to be reordered. This is fairly straightforward, as we only need tosequentially search the file and, if Number in Stock is less than the Reorder Level,make a note of the item and the supplier and output the details.The problem is when we check the stock the next day, we will create a new orderbecause the stock that has been ordered has not been delivered. To overcome this wecould introduce a new field called On Order of type Boolean. This can be set to Truewhen an order has been placed and reset to False when an order has been delivered.Unfortunately it is not that easy.The original software is expecting the original seven fields not eight fields. Thismeans that the software designed to manipulate the original file must be modified toread the new file layout.Further ad hoc enquiries are virtually impossible. What happens if management askfor a list of best selling products? The file has not been set up for this and to change itso that such a request can be satisfied in the future involves modifying all existingsoftware. Further, suppose we want to know which products are supplied by Food &Drink Ltd.. In some cases the companys name has been entered as Food & DrinkLtd., sometimes as Food and Drink Ltd. and sometimes the full stop after Ltd hasbeen omitted. This means that a match is very difficult because the data isinconsistent. Another problem is that each time a new product is added to thedatabase both the name and address of the supplier must be entered. This leads toredundant data or data duplication.The following example, shown in Fig. 3.6.a.1, shows how data can be proliferatedwhen each department keeps its own files. 4.6 - 1
  • 2. File containing Stock Programs to Code, Description, Purchasing place orders Re-order level, Cost Department when stocks are Price, Sale Price low Supplier name and address, etc File containing Stock Programs to Code, Description, Sales record orders Number sold, Sale Department from customers Price, Customer name and address, etc. File containing Programs to Customer name and Accounts record accounts address, amount Department of customers owing, dates of orders, etc. Fig. 3.6.a.1This method of keeping data uses flat files. Flat files have the following limitations.  Separation and isolation of data Suppose we wish to know which customers have bought parts produced by a particular supplier. We first need to find the parts supplied by a particular supplier from one file and then use a second file to find which customers have bought those parts. This difficulty can be compounded if data is needed from more than two files.  Duplication of data Details of suppliers have to be duplicated if a supplier supplies more than one part. Details of customers are held in two different files.  Duplication is wasteful as it costs time and money. Data has to be entered more than once, therefore it takes up time and more space.  Duplication leads to loss of data integrity. What happens if a customer changes his address? The Sales Department may update their files but the Accounts Department may not do this at the same time. Worse still, suppose the Order Department order some parts and there is an increase in price. The Order Department increases the Cost and Sale prices but the Accounts Department do not, there is now a discrepancy. 4.6 - 2
  • 3.  Data dependence Data formats are defined in the application programs. If there is a need to change any of these formats, whole programs have to be changed. Different applications may hold the data in different forms, again causing a problem. Suppose an extra field is needed in a file, again all applications using that file have to be changed, even if they do not use that new item of data.  Incompatibility of files Suppose one department writes its applications in COBOL and another in C. Then COBOL files have a completely different structure to C files. C programs cannot read files created by a COBOL program.  Fixed queries and the proliferation of application programs File processing was a huge advance on manual processing of queries. This led to end-users wanting more and more information. This means that each time a new query was asked for, a new program had to be written. Often, the data needed to answer the query were in more than one file, some of which were incompatible.To try to overcome the search problems of sequential files, two types of databasewere introduced. These were hierarchical and network databases. Examples of theseare shown in Fig. 3.6.a.2 and Fig. 3.6.a.3 respectively. Employee Part-Time Full-TimeCleaner Cashier Hourly Paid Salaried Tool Setter Cashier Manager Supervisor Fig. 3.6.a.2 Hierarchical Database 4.6 - 3
  • 4. Smart Cards Definitions Applications Electronic Purse Visa AMERKORE Mondex Aston NatWest International University Access Travelling Control Fig. 3.6.a.3 Network DatabaseThe hierarchical model can still lead to inconsistent and redundant data. A networkdatabase is similar to an hierarchical one, except that it has more complex pointers.An hierarchical database allows movement up and down the tree like structure. Anetwork database allows movement up, down and across the tree like structure. Thediagram in Fig. 3.6.a.3 shows how complex the pointers can become. This makes itvery difficult to maintain a network database. 4.6 - 4
  • 5. 3.6 (b) Relational Databases and NormalisationNote: When reading this Section you may wish to turn off the facility that underlinesspelling mistakes. This can be done by choosing Options… from the Tools menu andclicking on the Spelling and Grammar tab. Then check the boxes Hide spellingerrors on this document and Hide grammatical errors in this document. This is sothat you can see the keys.Consider the following delivery note from Easy Fasteners Ltd. Easy Fasteners Ltd Old Park, The Square, Berrington, Midshire BN2 5RG To: Bill Jones No.: 005 London Date: 14/08/01 England Product No. Description 1 Table 2 Desk 3 Chair Fig. 3.6. (b)1In this example, the delivery note has more than one part on it. This is called arepeating group. In the relational database model, each record must be of a fixedlength and each field must contain only one item of data. Also, each record must beof a fixed length so a variable number of fields is not allowed. In this example, wecannot say let there be three fields for the products as some customers may ordermore products than this and other fewer products. So, repeating groups are notallowed.At this stage we should start to use the correct vocabulary for relational databases.Instead of fields we call the columns attributes and the rows are called tuples. Thefiles are called relations (or tables).We write the details of our delivery note as DELNOTE(Num, CustName, City, Country, (ProdID, Description))where DELNOTE is the name of the relation (or table) and Num, CustName, City,Country, ProdID and Description are the attributes. ProdID and Description are putinside parentheses because they form a repeating group. In tabular form the data maybe represented by Fig. 3.6 (b)2. 4.6 - 5
  • 6. Num CustName City Country ProdID Description005 Bill Jones London England 1 Table 2 Desk 3 Chair Fig. 3.6 (b)2This again shows the repeating group. We say that this is in un-normalised form(UNF). To put it into 1st normal form (1NF) we complete the table and identify a keythat will make each tuple unique. This is shown in Fig. Fig. 3.6 (b)3.Num CustName City Country ProdID Description005 Bill Jones London England 1 Table005 Bill Jones London England 2 Desk005 Bill Jones London England 3 Chair Fig 3.6 (b)3To make each row unique we need to choose Num together with ProdID as the key.Remember, another delivery note may have the same products on it, so we need to usethe combination of Num and ProdID to form the key. We can write this as DELNOTE(Num, CustName, City, Country, ProdID, Description)To indicate the key, we simply underline the attributes that make up the key.Because we have identified a key that uniquely identifies each tuple, we haveremoved the repeating group.Definition of 1NF A relation with repeating groups removed is said to be in First Normal Form (1NF). That is, a relation in which the intersection of each tuple and attribute (row and column) contains one and only one value.However, the relation DELNOTE still contains redundancy. Do we really need torecord the details of the customer for each item on the delivery note? Clearly, theanswer is no. Normalisation theory recognises this and allows relations to beconverted to Third Normal Form (3NF). This form solves most problems. (Note:Occasionally we need to use Boyce-Codd Normal Form, 4NF and 5NF. This is rareand beyond the scope of this syllabus.)Let us now see how to move from 1NF to 2NF and on to 3NF.Definition of 2NF A relation that is in 1NF and every non-primary key attribute is fully dependent on the primary key is in Second Normal Form (2NF). That is, all the incomplete dependencies have been removed. 4.6 - 6
  • 7. In our example, using the data supplied, CustName, City and Country depend only onNum and not on ProdID. Description only depends on ProdID, it does not depend onNum. We say that Num determines CustName, City, Country ProdID determines Descriptionand write Num → CustName, City, Country ProdID → DescriptionIf we do this, we lose the connection that tells us which parts have been delivered towhich customer. To maintain this connection we add the dependency Num, ProdID → 0 (Dummy functional dependency)We now have three relations. DELNOTE(Num, CustName, City, Country) PRODUCT(ProdID, Description) DEL_PROD(Num, ProdID)Note the keys (underlined) for each relation. DEL_PROD needs a compound keybecause a delivery note may contain several parts and similar parts may be on severaldelivery notes. We now have the relations in 2NF.Can you see any more data repetitions? The following table of data may help.Num CustName City Country ProdID Description005 Bill Jones London England 1 Table005 Bill Jones London England 2 Desk005 Bill Jones London England 3 Chair008 Mary Hill Paris France 2 Desk008 Mary Hill Paris France 7 Cabinet014 Anne Smith New York USA 5 Cabinet002 Tom Allen London England 7 Cupboard002 Tom Allen London England 1 Table002 Tom Allen London England 2 DeskCountry depends on City not directly on Num. We need to move on to 3NF.Definition of 3NFA relation that is in 1NF and 2NF, and in which no non-primary key attribute istransitively dependent on the primary key is in 3NF. That is, all non-key elements arefully dependent on the primary key. 4.6 - 7
  • 8. In our example we are saying Num → CustName, City, Countrybut it is City that determines Country, that is City → Countryand we can write Num → City → Country Num → CustNameWe say that Num transitively functionally determines Country.Removing this transitive functional determinacy, we have DELNOTE(Num, CustName, City) CITY_COUNTRY(City, Country) PRODUCT(ProdID, Description) DEL_PROD(Num, ProdID)Let us now use the data above and see what happens to it as the relations arenormalised. 4.6 - 8
  • 9. 1NFDELNOTENum CustName City Country ProdID Description005 Bill Jones London England 1 Table005 Bill Jones London England 2 Desk005 Bill Jones London England 3 Chair008 Mary Hill Paris France 2 Desk008 Mary Hill Paris France 7 Cupboard014 Anne Smith New York USA 5 Cabinet002 Tom Allen London England 7 Cupboard002 Tom Allen London England 1 Table002 Tom Allen London England 2 Desk Convert to 2NFDELNOTE PRODUCTNum CustName City Country ProdID Description005 Bill Jones London England 1 Table008 Mary Hill Paris France 2 Desk014 Anne Smith New York USA 3 Chair002 Tom Allen London England 7 Cupboard 5 CabinetDEL_PRODNum ProdID005 1005 2005 3008 2008 7014 5002 7002 1002 2 Convert to 3NF 4.6 - 9
  • 10. DELNOTE DEL_PRODNum CustName City Num ProdID005 Bill Jones London 005 1008 Mary Hill Paris 005 2014 Anne Smith New York 005 3002 Tom Allen London 008 2 008 7 014 5 002 7 002 1 002 2PRODUCT CITY_COUNTRYProdID Description City Country1 Table London England2 Desk Paris France3 Chair New York USA7 Cupboard5 CabinetNow we can see that redundancy of data has been removed.In tabular form we haveUNF DELNOTE(Num, CustName, City, Country, (ProdID, Description))1NF DELNOTE(Num, CustName, City, Country, ProdID, Description)2NF DELNOTE(Num, CustName, City, Country) PRODUCT(ProdID, Description) DEL_PROD(Num, ProdID)3NF DELNOTE(Num, CustName, City) CITY_COUNTRY(City, Country) PRODUCT(ProdID, Description) DEL_PROD(Num, ProdID)In this Section we have seen the data presented as tables. These tables give us a viewof the data. The tables do NOT tell us how the data is stored in the computer, whetherit be in memory or on backing store. Tables are used simply because this is how users 4.6 - 10
  • 11. view the data. We can create new tables from the ones that hold the data in 3NF.Remember, these tables simply define relations.Users often require different views of data. For example, a user may wish to find outthe countries to which they have sent desks. This is a simple view consisting of onecolumn. We can create this table by using the following relations (tables). PRODUCT to find ProdID for Desk DEL_PROD to find Num for this ProdID DELNOTE to find City corresponding to Num CITY_COUNTRY to find Country from CityHere is another example of normalisation.Films are shown at many cinemas, each of which has a manager. A manager maymanage more than one cinema. The takings for each film are recorded for eachcinema at which the film was shown.The following table is in UNF and uses the attribute names FID Unique number identifying a film Title Film title CID Unique string identifying a cinema Cname Name of cinema Loc Location of cinema MID Unique 2-digit string identifying a manager MName Managers name Takings Takings for a film FID Title CID Cname Loc MID MName Takings 15 Jaws TF Odeon Croyden 01 Smith £350 GH Embassy Osney 01 Smith £180 JK Palace Lye 02 Jones £220 23 Tomb Raider TF Odeon Croyden 01 Smith £430 GH Embassy Osney 01 Smith £200 JK Palace Lye 02 Jones £250 FB Classic Sutton 03 Allen £300 NM Roxy Longden 03 Allen £290 45 Cats & Dogs TF Odeon Croyden 01 Smith £390 LM Odeon Sutton 03 Allen £310 56 Colditz TF Odeon Croyden 01 Smith £310 NM Roxy Longden 03 Allen £250Converting this to 1NF can be achieved by filling in the blanks to give the relation 4.6 - 11
  • 12. FID Title CID Cname Loc MID MName Takings 15 Jaws TF Odeon Croyden 01 Smith £350 15 Jaws GH Embassy Osney 01 Smith £180 15 Jaws JK Palace Lye 02 Jones £220 23 Tomb Raider TF Odeon Croyden 01 Smith £430 23 Tomb Raider GH Embassy Osney 01 Smith £200 23 Tomb Raider JK Palace Lye 02 Jones £250 23 Tomb Raider FB Classic Sutton 03 Allen £300 23 Tomb Raider NM Roxy Longden 03 Allen £290 45 Cats & Dogs TF Odeon Croyden 01 Smith £390 45 Cats & Dogs LM Odeon Sutton 03 Allen £310 56 Colditz TF Odeon Croyden 01 Smith £310 56 Colditz NM Roxy Longden 03 Allen £250This is the relation R(FID, Title, CID, Cname, Loc, MID, MName, Takings) Title is only dependent on FID Cname, Loc, MID, MName are only dependent on CID Takings is dependent on both FID and CIDTherefore 2NF is FILM(FID, Title) CINEMA(CID, Cname, Loc, MID, MName) TAKINGS(FID, CID, Takings)In Cinema, the non-key attribute MName is dependent on MID. This means that it istransitively dependent on the primary key. So we must move this out to get the 3NFrelations FILM(FID, Title) CINEMA(CID, Cname, Loc, MID) TAKINGS(FID, CID, Takings) MANAGER(MID, MName) 4.6 - 12
  • 13. 3.6 (c) Entity-Relationship (E-R) DiagramsEntity-Relationship (E-R) diagrams can be used to illustrate the relationships betweenentities. In the earlier example we had the four relations DELNOTE(Num, CustName, City) CITY_COUNTRY(City, Country) PRODUCT(ProdID, Description) DEL_PROD(Num, ProdID)In an E-R diagram DELNOTE, CITY_COUNTRY, PRODUCT and DEL_PROD arecalled entities. Entities have the same names as relations but we do not usually showthe attributes in E-R diagrams.We now consider the relationships between the entities.Each DELNOTE can be for only one CITY_COUNTRY because a City only occurs once on DELNOTEEach CITY_COUNTRY may have many DELNOTE because a City may occur on more than one DELNOTEEach DELNOTE will have many DEL_PROD Num in DELNOTE could occur more than once in DEL_PRODEach DEL_PROD will be for only one DELNOTE because each Num in DEL_PROD can only occur once in DELNOTEEach PRODUCT will be on many DEL_PROD PRODUCT can occur more than once in DEL_PRODEach DEL_PROD will have only one PRODUCT because each ProdID in DEL_PROD can only occur once in PRODUCTThe statements show two types of relationship. There are in fact four altogether.These are one-to-one represented by one-to-many represented by many-to-one represented by many-to-many represented byFig. 3.6 (c)1 is the E-R diagram showing the relationships between DELNOTE,CITY_COUNTRY, PRODUCT and DEL_PROD. 4.6 - 13
  • 14. DELNOTE CITY_COUNTRY DEL_PROD PRODUCT Fig. 3.6 (c)1If the relations are in 3NF, the E-R diagram will not contain any many-to-manyrelationships. If there are any one-to-one relationships, one of the entities can beremoved and its attributes added to the entity that is left.Let us now look at our solution to the cinema problem which contained the relations FILM(FID, Title) CINEMA(CID, Cname, Loc, MID) TAKINGS(FID, CID, Takings) MANAGER(MID, MName)in 3NF.We have the following relationships. takes FILM TAKINGS is forconnected by FID takes CINEMA TAKINGS is forconnected by CID manages MANAGER CINEMA managed byconnected by MIDThese produce the ERD shown in Fig. 3.6 (c)2. 4.6 - 14
  • 15. CINEMA MANAGER TAKINGS FILM Fig. 3.6 (c)2In this problem we actually have the relationship CINEMA shows many FILMs FILM is shown at many CINEMAsThat is CINEMA FILMBut this cannot be normalised to 3NF because it is a many-to-many relationship.Many-to-many relationships are removed by using a link entity as shown here. CINEMA LINK_ENTITY FILMIf you now look at Fig. 3.6.c.2, you will see that the link entity is TAKINGS.Form DesignSection 2.1 (c) discussed the design of screens and forms. All that was said in thatsection applies to designing forms for data entry, data amendments and for queries.The main thing to remember when designing screen layouts is not to fill the screentoo full. You should also make sure that the sequence of entering data is logical andthat, if there is more than one screen, it is easy to move between them.Let us consider a form that will allow us to create a new entry in DELNOTE whichhas the attributes Num, CustName, City. Num is the key and, therefore, it should becreated by the database system. Fig. 3.6 (c)3 shows a suitable form. 4.6 - 15
  • 16. Entered bythe system Appears automatically when City is completed if Can use in database.drop down lists to complete Add details Cancel and Close the to database leave blank form form on the screen Fig. 3.6 (c)3 With this form, if a new City is input the user can input the Country and the City_Country table will be updated. If the City exists in the database, then Country will appear automatically. Now let us design a form to allow a user to input a customers order. In this case we shall need to identify the customer before
  • Related Search
    We Need Your Support
    Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

    Thanks to everyone for your continued support.

    No, Thanks