Database Management Systems: 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………………………………..13
References17
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 ApproachAccording 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. Entity relationship diagram (ERD), identifying entities in the problem domain and showing relationships among entitiesIdentifying entities and their attributesEntity Agentagent_id
Commission_rate
W_phone
M_phone
l-name
surname
f-name
Commission_code
Agent_contacts
Agent
Agent_name
total_monthly_sales
base_salary
payment_date
cheque_number
Client_id
Entity Client
Client-id
First_name
property_id
client_phone
mobile-phone
last_name
surname
Client
Client-type
address
Entity PropertyProperty_id
location
Agreed_price
listingForSale_date
Year_build
size
Asking_price
House_design
Property
Buyer_id
buter
Deposit_paid
Settlement_date
room_numbers
Relationship Among EntitiesDeriving Entity RelationshipsAgent Property Client
Agent Values Pays/Receives-payment
Property valued Sold/bought
Client Books appointment Buys/sells Entity RelationshipsAgent
Values
Property
Client
Agent
Property
Agent
Valued
Pays or Paid
Book Appoint
Client
Agent
Client
Buys/Sells
Bought/sold
Property
Client
Property
Entity Relationship DiagramA_name
agent_id
chequeNo
client_id
No.Rms
Yr_build
H_design
ListDate
totalMonthsales
BaseSalary
SettlementDate
description
AgreePrice
phone
Client_id
Pro_id
Address
mobile
f_name
surname
l_name
C_type
Address
rate
Mobile
payDate
Pro_id
Deposit
AskPrice
size
location
Paid/pays/Book Appointment
Client
Buys/sells/bought/sold
Property
Valued/Values
Agent
Relational Model of ERDProperty Property_id (PK) Size Location Description House_design NumberOfRooms Year_build ListingForSale_date Asking_price Agreed_price Deposit_paid Settlement_Date Agent_id (FK) Client_id (FK)
Agent Agent_id (PK) f_name surname l_name work_phone mobile_phone Cheque_number Payment_date Base_salary Commission_code Commission_rate Total_Monthly_salary client_id (FK)
Pays or Paid/ Book Appointme
Buys or Sells/Bought
Values/Valued
Client Client_id (PK) Client_type f_name surname l_name Mobile_phone Telephone Postal_Address Physical_address Property_id (FK)
3. Creating and Populating tables agent, client and property
— phpMyAdmin SQL Dump
— version 4.0.4.1
— http://www.phpmyadmin.net
— Host: 127.0.0.1
— Generation Time: Apr 20, 2014 at 01:10 PM
— Server version: 5.5.32
— PHP Version: 5.4.19
SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
— Database: `pr4u`
CREATE DATABASE IF NOT EXISTS `pr4u` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `pr4u`;
/* Table structure for table `agent`*/
CREATE TABLE IF NOT EXISTS `agent` (
`agent_id` varchar(20) NOT NULL,
`f_name` varchar(20) NOT NULL,
`surname` varchar(20) NOT NULL,
`l_name` varchar(25) NOT NULL,
`work_phone` int(25) NOT NULL,
`mobile_phone` int(20) NOT NULL,
`base_salary` int(20) NOT NULL,
`total_monthly_sales` int(25) NOT NULL,
`commission_code` varchar(20) NOT NULL,
`commission_rate` int(25) NOT NULL,
`cheque_number` varchar(25) NOT NULL,
`payment_date` varchar(25) NOT NULL,
`client_id` varchar(25) NOT NULL,
PRIMARY KEY (`agent_id`),
KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Populating table ‘agent’*/
INSERT INTO `agent` (`agent_id`, `f_name`, `surname`, `l_name`, `work_phone`, `mobile_phone`, `base_salary`, `total_monthly_sales`, `commission_code`, `commission_rate`, `cheque_number`, `payment_date`, `client_id`) VALUES
(‘A1’, ‘John’, ‘Smith’, ‘Clark’, 2012181205, 712181205, 800, 500000, ‘A’, 0, ‘A1C3245′, ’10/04/2014’, ”),
(‘A2’, ‘Sussan’, ‘Jane’, ‘Doe’, 202568978, 714559860, 1000, 800000, ‘C’, 0, ‘A2C4586′, ’30/04/2014’, ‘C1’),
(‘A3’, ‘Mathews’, ‘Ken’, ‘Johnstone ‘, 208579369, 832569875, 12000, 1000000, ‘D’, 0, ‘A3C4587′, ’29/04/2014’, ‘C1’);
/* Table structure for table `client`*/
CREATE TABLE IF NOT EXISTS `client` (
`client_id` varchar(20) NOT NULL,
`f_name` varchar(20) NOT NULL,
`surname` varchar(20) NOT NULL,
`l_name` varchar(20) NOT NULL,
`client_type` varchar(10) NOT NULL,
`mobile_phone` int(20) NOT NULL,
`home_phone` int(20) NOT NULL,
`address` int(20) NOT NULL,
`city` varchar(20) NOT NULL,
`property_id` varchar(20) NOT NULL,
KEY `property_id` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
/*Populating table ‘client’*/
—
INSERT INTO `client` (`client_id`, `f_name`, `surname`, `l_name`, `client_type`, `mobile_phone`, `home_phone`, `address`, `city`, `property_id`) VALUES
(‘C1’, ‘Richard’, ‘Klein’, ‘Joe’, ‘Seller’, 2147483647, 2147483647, 40325, ‘Liverpool’, ‘P5’),
(‘C2’, ‘Annette’, ‘Mary’, ‘Wycliffe’, ‘Buyer’, 23569856, 35687927, 4543256, ‘London’, ‘P3’);
/*Table structure for table `property`*/
CREATE TABLE IF NOT EXISTS `property` (
`property_id` varchar(20) NOT NULL,
`location` varchar(20) NOT NULL,
`size` int(10) NOT NULL,
`house_design` varchar(20) NOT NULL,
`deposit_paid` int(20) NOT NULL,
`settlement_date` date NOT NULL,
`room_numbers` int(20) NOT NULL,
`year_build` date NOT NULL,
`listing_year` date NOT NULL,
`asking_price` int(25) NOT NULL,
`agreed_price` int(25) NOT NULL,
PRIMARY KEY (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Populating table ‘property’*/
INSERT INTO `property` (`property_id`, `location`, `size`, `house_design`, `deposit_paid`, `settlement_date`, `room_numbers`, `year_build`, `listing_year`, `asking_price`, `agreed_price`) VALUES
(‘P1’, ‘London’, 0, ‘Bungalow’, 50000, ‘2014-04-30’, 3, ‘2010-12-15’, ‘2014-04-01’, 200000, 150000),
(‘P2’, ‘Birmigham’, 0, ‘Apartment’, 100000, ‘2014-04-29’, 30, ‘2012-08-16’, ‘2014-02-04’, 1000000, 850000);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
ReferencesKumar, PS. N.d. Database Systems
Robbins, RJ. (1995). Database Fundamentals. Johns Hopkins University
Sirpal, S. N.d. Intro to RDBMS-OODBMS-ORDBMS
Bergholt, L., Due, JS., Hohn, RT., Knudsen, JL., Nielsen, KH., Olesen, TS. & Pedersen, ES. (1998). Database Management Systems: Relational, Object-Relational, and Object-Oriented Data Models, Centre for Objekt Teknology
Subau, G. (2007). Comparison of RDBMS, OODBMS and ORDBMS, Bucharest, Romania
Wang, LT. N.d. Object-Oriented DBMS Concepts, National University of Singapore
Güting, RH. (1994). An Introduction to Spatial Database Systems
Ramakrishnan, Gehrke, Hellerstein & Gray.(1992). Parallel Database Systems: The Future of High Performance Database Systems.
