Outcome Of Sections 2 And 3 example essay topic

DOUBLE D ESTATE AGENCY COMPANY ASSESSMENT PART 1 1.0 INTRODUCTION The first part of the formative assessment that deals with the case study of the Double D Estate Agency Company. The estate agency is situated in Yorkshire with their head office in Melbourne, Australia. The company handles properties that are for sale or rental. Potential customers become aware of the properties as a result of advertising carried out by Double D. It uses both the traditional printed media, such as newspapers and brochures, and the new electronic media, such as the Internet and local radio.

The following parts need to be completed for this assignment; The contribution that Conceptual design, Logical design (deriving tables from ER model and Normalisation top-down and bottom-up approaches) SECTION 1: Sales ERD given to you in week 4, for referencing. SECTION 2: a) Produce a set of relations (table definitions) from Sales ER model, in SECTION ONE, by following the rules for logical design. For each relation, clearly identify the PRIMARY KEY, ATTRIBUTES and any FOREIGN KEY (s). You are advised to show any workings. (10 marks) b) Check that these relations are in third normal form.

Here you will conduct top-down approach. (5 marks) SECTION 3: c) Use Sales Form in Appendix 3 to carry out a bottom-up normalisation exercise by using the data items on the form as attributes, and putting all of them into one table. Then normalise these into first, second, and then third normal form and finally, identify the relations, and any primary and foreign keys. Present your work on the Normalisation pro-forma - available from WebCT.

(10 marks) d) Analyse the outcomes of this normalisation exercise, and compare the results with the set of tables produced in SECTION 2. Discuss any changes to this consolidated model that you now wish to make, and why. (5 marks) 1 SECTION 4: Produce a full definition of the database that you will implement, by providing a full list of all tables, their primary and foreign keys (s), and the attributes and definitions, and 2 rows of data value for each table. This is the final specification of the database that will be implemented, and will be the 'consolidated's et of table definitions produced as the outcome of SECTIONS 2 and 3 above.

(5 marks) A pro-forma is available to download from WebCT. SECTION 5: A discussion of the following: a kes to the design of a database. Answer the questions: Why undertake each technique, what feeds in to it (i.e. inputs), what are output (s), how can the result be verified, what is the role of the consolidation? Present example of work for each discussed technique. How have your knowledge, understanding and skills developed from Year One? 2 Section 1 Sales ERD Diagram 3 Section 2 Logical Design PROPERTY (propertyid, address, postcode, correspondantname, correspondantpostcode, propertytype, noofbedrooms, noofbathrooms, noofreceptions, heatingsystem, glazing, noof garage, parking, pricepaid, boughtwhen, throughagency, timescale, ownership, securitysystem, askingprice, tenure, saleboard) PROSPECTS (prospectid, firstname, surname, dob, address, home#, work#, mobile#, email#, postcode, besttimetocall, other details. positionofseller, solicitor name, firm name, firm address, phone#) APPOINTMENT (appointment#, valuationtime, valuationday, datebooked) SALES (propertyid, sales date) AUCTION (propertyid, auctiondate, time, date) SALE (propertyid, settlement date) SALES DOCUMENTATION (sdid, fees agreed, withdrawalfee, advertising, discounts, sellerspackissued, marketingbrand) APPOINTMENT (appointment#, prospectid, propertyid, valuationday, valuationtime, datebooked) PROPERTY (propertyid, mngpropertyid, address, postcode, propertytype, noofbedrooms, noofreceptions, noofbathrooms, heatingsystem, glazing, garage, parking, garden, pricepaid, boughtwhen, throughagency, timescale, ownership, securitysystem, askingprice, tenure, saleboard) SALES DOCUMENTATION (sdid, propertyid, withdrawalfee, fee agreed, discounts, advertising, sellerspackissued, marketingbrand) 4 Section 3 Normalisation Un-normalised Attributes 1 NF 2 NF 3 NF Relation fullname 1 customerid CUSTOMER fullname 2 fullname 1 home# fullname 2 work# home# mobile# work# besttimetocall mobile# timescale besttimetocall movingto timescale propertytype movingto noofbedrooms propertytype price noofbedrooms propertyaddress price postcode propertytype propertyid noofbedrooms propertyaddress noofreceptions postcode PROPERTY heatingsystem propertytype glazing noofbedrooms garage noofreceptions parking heatingsystem garden glazing tenure garage posofseller parking pricepaid garden boughtwhen sellerpriceexpect valuername valuationday valuationdate valuationtime datebooked appointment# feequoted valuername discounts valuationday APPOINTMENT sugaskingprice valuationdate appointment# auctionsale valuationtime customerid auctiondate datebooked propertyid location feequoted valuername time discounts valuationday withdrawalfee sugaskingprice valuationdate solicitor details withdrawalfee valuationtime datebooked feequoted discounts auctionsale sugaskingprice auctiondate withdrawalfee location time SALES Sale# solicitor# auctionsale auctiondate location time SOLICITOR solicitor# solicitor# The following information given in steps analyses the normalisation ex cerise that has been undertaken.

1 Identified all attributes and put then intothe first column. Recognise that we need to normalise against a key. 2 First Normal Form - Deal with the repeating groups, and therefore split the un-normalised relation so that we have relation in first normal form. 3 Second Normal Form - Those attributes which are specific to owner have been removed from the first relation 4 Third normal form - Is there any dependencies between non-key attributes? The results of the bottom up normalisation exercise suggested to me that several of the relations in Section Two can be altered by adding specific keys and attributes to make the model reasonable. Section 4 Relations Relation Name: CUSTOMER Attribute Primary Key?

("O) Foreign Key? ("O) Format Data values Customerid Fullname 1 Fullname 2 Home# Work# Mobile# Besttimetocall Timescale Movingto Propertytype Noofbedrooms Price [ NUMBER (2) VARCHAR 2 (10) VARCHAR 2 (10) VARCHAR 2 (15) VARCHAR 2 (15) VARCHAR 2 (11) NUMBER (4, 2) VARCHAR 2 (15) VARCHAR 2 (25) VARCHAR 2 (15) NUMBER (2) VARCHAR 2 (7) 1, 2 01132785000, 01132554879 01132569854, 01132635211 07798654123, 07869845212 18.00, 19.00 3 months, 6 months Head ingly, Mean wood Terraced, Semi-detached 4, 5 85,000, 95,995 Relation Name: PROPERTY Propertyid Propertyaddress Postcode Propertytype Noofbedrooms Noofreceptions Heatingsystem Glazing Garage Parking Tenure Positionofseller Garden Pricepaid Boughtwhen Sellerpriceexpectation [ NUMBER (2) VARCHAR 2 (40) VARCHAR 2 (8) VARCHAR 2 (15) NUMBER (2) NUMBER (2) VARCHAR 2 (15) VARCHAR 2 (10) VARCHAR (3) VARCHAR 2 (10) VARCHAR 2 (10) VARCHAR 2 (10) VARCHAR 2 (7) VARCHAR 2 (7) VARCHAR 2 (11) VARCHAR 2 (7) 2, 3 7 Awesome Drive, 19 Stone Road LS 5 3 RX, LS 4 7 HL Detached, Terraced Double, Double No, Yes On Street, On Street Freehold, Freehold Back, Front & Back 80,000,105,000 19 February 1996, 21 June 2000 97,000,125,000 Relation Name: APPOINTMENT Appointment# Customerid Propertyid Valuername Valuationday Valuationdate Valuationtime Datebooked Feequoted Discounts Suggestedaskingprice Withdrawalfee [ [ [ NUMBER (2) NUMBER (2) NUMBER (2) VARCHAR 2 (20) VARCHAR 2 (10) VARCHAR 2 (11) NUMBER (4, 2) VARCHAR 2 (11) VARCHAR 2 (4) VARCHAR 2 (4) VARCHAR 2 (7) VARCHAR 2 (4) 1, 2 1, 2 2, 3 Daniel Stevenson, Matthew Night Monday, Thursday 95,000,130,000 0, 0 Relation Name: SALE Sale# Auctionsale Auctiondate Location Time [ NUMBER (2) VARCHAR 2 (10) VARCHAR 2 (11) VARCHAR 2 (25) NUMBER (4, 2) 1, 2 Relation Name: SOLICITOR Solicitor# [ NUMBER (2) 1, 2 Conclusion Section 5 Conceptual design is used to represent a user's information requirements. Information such as propertyaddress, postcode etc, are grouped to form a single entity i. e., PROPERTY. Once all the required information is obtained entities can be identified. These are then placed under 'rules' to identify a relationship between them. This process is continued until a complete set of entities are identified and a complete ER model is produced (output.) Logical design is used to derive tables from the model.

This stage is necessary because it will identify the relations, attributes, unique identifiers and any other key attributes. Logical design can be produced using two methods: Top-Down Approach: you are able to identify links between the entities. Bottom-Up Approach: you are able to identify links between the attributes. The top-down approach has produced a set of tables which covers a wider scope than the bottom-up exercise. Consolidation is a key term used in the above stages.

This is to ensure that the final system to be implemented is full combined. I feel my knowledge and understanding has improved since year one. I am now able to explain terms with more detail and correctness. The normalisation stage was difficult to understand at first, I feel quite comfortable with the understanding.