Database Management System (Coursework Sample)
Description of Assessment Requirements
Students are required to produce written answers to the requirement set on Database Management Systems.
Through addressing the set requirements of the assignment, students are expected to demonstrate course concepts and ideas studied in the course.
The assignment requires students to provide both theoretical and practical knowledge, and therefore requires good quality research.
The work produced should typically include:
1. Table of contents
2. Responses to the requirements set
3. Listing of references/bibliography
Assignment brief:
Properties-R-4-Us, (PR4U), is a real estate agency serving London. It deals with the buying and selling of residential properties. PR4U has many agents whose responsibilities include seeking properties to list for sale, and finding buyers for these properties. However, these are not required to be included in the database. PR4U’s clients are the sellers of properties, as well as the buyers.
Payments to the agents are made as follows:
- Each agent has a monthly base wage, varying from £800 to £1500 per month, set by the management of PR4U and depends on the agents experience and position.
- A commission rate that is applied to the agents total monthly sales. This figure is also set by the management of PR4U.
Commission code for Agent Percentage of Total Sales
A 0.22%
B 0.24%
C 0.26%
D 0.28%
E 0.30%
On the third business day of each month, each agent receives a pay cheque which includes the base figure plus commission. For example, Francis has a base salary of £1500 per month, has a commission code of C, and achieved monthly sales of £1,000,000.
Thus her monthly pay cheque = base + rate X monthly-sales
= £1500 + .26% X £1,000,000
= £4,100
Details that need to be recorded includes the agents-name, cheque-number, payment date, base salary, total-monthly sales and commission paid. Your tables should contain the data which would allow commission calculations to be performed. (Commission is paid only when settlement occurs; which is when legal contracts are exchanged).
Other information which needs to be considered for storage includes:
Agents-work-phone, agents-mobile-phone, client-id, client-first-name, client-surname, client-address, client-phone, client-mobile-phone, property-location, property-size, house-description, number-rooms, year-house-built, listing-date (of the property for sale), asking-price, sale-date, buyer, agreed-price, settlement-date and deposit-paid.
PR4U receive calls from sellers to find out how much their property can be valued at. Each call is received by receptionist and entered onto the database and a schedule to visit properties to estimate the value of the property would be generated by the system. This can be seen by each agent.
On visit to property, agents estimate the value of the property, date of visit along with its description like property-location, property-size, house-description, number-rooms, year-house-built etc would be recorded on the database.
If clients want to put their properties on the market, the agents can do so and publish on PR4U website.
PR4U receives calls and/or emails from prospective buyers and agents must arrange property viewing for their clients. On visit to the property, clients can make an offer, which would be passed on to the seller. After negotiation with the seller, if sale goes through, the PR4U get a 3% commission from the seller on the sold price. These details would be recorded on the database as well.
Each client must be registered on the system with their own username and password and they can submit all their property preferences. Whenever, the properties come up for sale and if they suit the clients’ preferences, they could be automatically emailed to the clients.
In addition to all above requirements, PR4U wants the following reports in different formats. Please note that you can make sensible assumptions about formatting requirements. Reports should include all relevant data for the management.
• List of all properties with all details
• List of all properties sold by weekly, monthly and annually
• List of all clients with their property preferences
• List of all visits made by agents to estimate the property value along with property details
• List of all visits made by agents for viewing the property with all property details.
• All the sales made monthly in XML format so that it could be passed on to HM Land Registry.
Assignment tasks
1) Produce an Entity Relationship Diagram (ERD) identifying the entities in the problem domain and showing the relationships among entities.
2) Create a relational model for the ERD produced above in task b, showing appropriately named relations, and clearly identifying attributes, the primary and foreign keys.
3) Create and populate the tables above with sample data. Write relevant SQL statements for the reports
An Evaluation of the Property-4-R-Us real estate agency’s case
Omari Simba
Unit/Subject Name and Code
Instructor’s Name
Institution’s Name
Department Name
Date
Table of Contents TOC \o "1-3" \h \z \u Introduction PAGEREF _Toc385800798 \h 3Different DBMS approaches and the actual DBMS employing them PAGEREF _Toc385800799 \h 3File System Approach to data storage PAGEREF _Toc385800800 \h 4Relational Database Management Systems (RDBMS) PAGEREF _Toc385800801 \h 4Object-Oriented Database Management System Approach PAGEREF _Toc385800802 \h 5Object-Relational Database Management System (ORDMS) approach PAGEREF _Toc385800803 \h 5Other Database Management Systems PAGEREF _Toc385800804 \h 6Conclusion PAGEREF _Toc385800805 \h 6Entity relationship diagram (ERD), identifying entities in the problem domain and showing relationships among entities7Identifying entities and their attributes7Entity Agent7Entity Client8Entity Property9Relationship Among Entities9Deriving Entity Relationships9Entity Relationships10Entity Relationship Diagram11Relational Model of ERD12 Creating and populating tables property, entity and agent………………………………..13References17
Introduction
Many organizations are rapidly embracing management information systems as opposed to the traditional off-the-shelf filing, prompting an urgent rise for the need of data warehousing. The data fed into the management information systems and the information produced as the processed data need to be stored for future retrieval, aid in further data processing and act as future reference. Data can be stored in various forms in the computer. However, the file system and the database system approaches are the main forms of data storage mechanisms. The efficient mechanism for data storage among the two is the database system. A database is a collection of real-world related data, collected and maintained to represent a given organisation’s information needs (Kumar, N.d, p. 2). The users can interact with the database by creating and maintaining its data through the database management system which is simply a set of programs that provides abstraction between the database and the user (Robbins, 1995, p. 3). Different database management systems (DBMS) are in existence, employing the Relational database approach, Object-oriented database approach, object-relational approach or the file system approach.
Different DBMS approaches and the actual DBMS employing them
Every actual database management system implementation has a theory of operation behind it. The theory models and simulates the way data is stored and manipulated through update, search, insertion, deletion and retrieval. The theory lays down the framework for the operation of the entire database management system. It is important therefore to explore the various theories that have been advanced to simulate the working of the various database management systems.
In any one specific approach, the following requirements must be met to qualify that given database management system as effective: efficiency should be met in manipulation of very large data, fault-tolerance and resilience in case of hardware or software failure without inconsistence and loss of data, capability to incorporate access control mechanisms to allow only authorized personnel to access it, have a long perseverance ability transcending time and nature of programs accessing its contents (Sirpal, N.d, P.4).
* File System Approach to data storage
The file system is part of the operating system that manages the storage, manipulation and retrieval of files. The files are records of interrelated data/information. However, the file system has been found to have the following main flaw: data redundancy.
* Relational Database Management Systems (RDBMS)
In relational database management systems, the stored data is modelled into relations that exist between attributes of entities, which are real world objects. The attributes of that given entity is represented in the columns while the instance of the entity representing the real world object is represented by the records; rows of the table. According to Bergholt et al (1998), a relational database management system is a system that organizes data/information into two-dimensional relations as represented in the rows and columns of the database table (p.11). The record, also known as a tuple, contains information about a given instance of an entity with the component of each cell being of a primitive data type; integer (int), character (char), float, strings. (Subau, 2007, p.2). The RDBMS is advantageous in that it provides an easier approach in the analysis, design, implementation, testing, maintenance and use of the database, excellent conceptual simplification, excellent SQL querying ability which in combination produces an efficient database management system (Sirpal, N.d, p.30). It however has the following advantages: lack of enough space to handle the storage of media files like audio and visual files, the restriction of data being stored in rows and columns and poor design possibility (Sirpal. N.d, P. 30).
* Object-Oriented Database Management System Approach
According to Bergholt et al (1998) the entity in the RDMS is modelled to be an object in the OODBMS, having the attributes of procedures, a group of values or simple values and pointers/references to other objects (p. 15). Data is stored along with those procedures that can act on that data (Subau, 2007, p.3). Structural properties and Behavioural properties of an object are defined by a finite set of attributes and finite set of methods that are uniquely inherent in any particular object ( Wang, N.d, p. 6). The OODBMS was designed to handle complex and large data while ensuring consistent, secure, extensible and controlled data management, controlled and extensible data management, data independent mechanisms that would allow the implementation of the object-oriented model of database design (Sirpal, N.d, p. 31). The theory also inherits other powerful object-oriented language capabilities like data encapsulation, inheritance, polymorphism and use of objects to represent real world entities, making it better than RDBMS (Sirpal, N.d, p.31).
* Object-Relational Database Management System (ORDMS) approach
The object-relational approach is an attempt to extend the functionalities of relational database managements (RDBMS) systems with the object-oriented approach of database design by attempting to bridge the gap between the two (Wang, N.d, p. 4). Both features from the two approaches must be combines to produce a real ORDBMS (Bergholt et al., 1998).
* Other Database Management Systems
Apart from the three main database design approaches discussed above, other minor database design theories have been advanced. Güting (1994) proposes a spatial database model system that supports spatial data types and querying languages to achieve spatial join methods and indexing suitable for geographic information system applications (p. 1). Parallel and distributed database management systems have also been proposed. Parallelism achieved through partition and pipelining techniques have proven to achieve database access efficiency through indexing, querying and data loading experimental techniques (Ramakrishnan et al. 1992). Combining parallelism in a distributed environment can highly increase the efficiency of access to any distributed DBMS.
Conclusion
Object-Relational approach to database management system design proves to be the most efficient design mechanism for the database management system in our case. To improve it further, aspects of spatial, parallelism and distributed computing environments can be added to the database management system to make it more efficient to handle virtually any data, from any geographical area in an efficient manner. A spatial distributed parallel ORDMS can be proposed to be deployed in managing the data warehousing needs of Property-4-R-Us real estate agency.
1 Entity relationship diagram (ERD), identifying entities in the problem domain and showing relationships among entities
* Identifying entities and their attributes
* Entity Agent
agent_id
Commission_rateW_phoneM_phonel-namesurnamef-nameCommission_codeAgent_contactsAgentAgent_nametotal_monthly_salesbase_salarypayment_datecheque_number
Client_id
* Entity Client
Client-id
First_name
property_id
client_phonemobile-phonelast_namesurname
Client
Client-typeaddress
* Entity Property
Property_id
locationAgreed_price
listingForSale_dateYear_build
sizeAsking_price
House_design
PropertyBuyer_id
buter
Deposit_paid
Settlement_date
room_numbers
* Relationship Among Entities
* Deriving Entity Relationships
Agent
Property
Client
Agent
Values
Pays/Receives-payment
Property
valued
Sold/bought
Client
Books appointment
Buys/sells
* Entity Relationships
AgentValuesProperty
ClientAgent
PropertyAgentValued
Pays or Paid
Book Appoint
ClientAgent
ClientBuys/SellsBought/soldPropertyClientProperty
2
* Entity Relationship Diagram
A_nameagent_id
chequeNoclient_idNo.RmsYr_build
H_designListDate
totalMonthsalesBaseSalarySettlementDatedescriptionAgreePricephoneClient_idPro_idAddressmobilef_namesurnamel_nameC_typeAddressrateMobilepayDatePro_idDepositAskPricesizelocationPaid/pays/Book AppointmentClientBuys/sells/bought/soldPropertyValued/ValuesAgent
* Relational Model of ERD
Property Property_id (PK) Size Location ...
Other Topics:
- The Test SpecificationsDescription: The test scripts below are meant to test the functionalities of an aircraft booking system. The system conforms to the SRS document with which the login details and the customer details are stored in a database. In a normal situation, the data is entered directly either by the user, admin or the customer ...2 pages/≈550 words| APA | Technology | Coursework |
- Test scriptDescription: The test scripts below are meant to test the functionalities of an aircraft booking system - Technology Coursework...2 pages/≈550 words| APA | Technology | Coursework |
- Introduction to java programmingDescription: Define method headers for computeAverageSales() and computeShipping Charges() according to the following requirements...2 pages/≈550 words| APA | Technology | Coursework |
