Scenario A – Car Dealership
Database management system is a very important aspect in the setting of any organization. It helps different companies to be able to gather information about their employees, stock and the operations of the activities conducted by the organization (Beynon, 2004). The database being designed here is for a car dealer and should be able to form an avenue for communication between the customers, the suppliers and the managers. The database to be designed should also be able to contain information about the different types of cars being stocked by the company and also be used by the company to manage their stock. The database is divided into three different sections namely the planning section, implementation section and the maintenance section. The planning section involves the procedure for gathering information about the database requirements while the implementation stage involves the procedures for putting the designed components into use. The maintenance stage is mainly concerned with ensuring that the database is always up to date and that it meets all its functionalities (Connolly, Thomas and Carolyn, 2002).
Car dealership involves all the operations that are involved in managing a fleet of cars in the company. The information system to be designed takes into consideration all the operations that any car provider needs to have so as to effectively manage their fleet of cars. Each car in the company is given a unique code that is used to uniquely identify all the cars. This dealer is mainly concerned with Honda cars and trucks. The information system to be designed is supposed to replace a paper based information system being used by the company. The paper-based information is manually operated and has proved to be ineffective in performing various functions for the company. This information system is going to have several interfaces that involve interactions with the managers, the suppliers, the clients and the employees of the company. Some of the important information that needs to be kept in the database include: the car manufacturer, year of manufacturer, model number, engine size, number of doors, number of passengers, drive type (whether automatic or manual/ 2 wheel drive or 4 wheel drive), fuel type used and the environmental working conditions of the car.
Part A: Information database planning system
This new system is supposed to effectively keep track of all the cars owned and managed by the company. This new system will affect both the senior mangers of the company and the junior employees. It is also likely to affect the suppliers and the customers of the company. In the new system, a client can easily log into the system and get the database that contains different categories of vehicles and choose one that best suits him/her. The managers will be able to know the performance of different brands and the ones that are fast moving so as to implement a good stock management system (Connolly, Thomas and Carolyn, 2002).
This system is set to come with many benefits to the company dealing with the cars, the suppliers and the customers of the company. The suppliers will be able to get information from the system about the performance of different models so that they can know on which models to supply more to the company. The managers will also be able to exercise a good stock management system as it will be very easy for them to keep track of the cars in their stock. The customers will also get to know which cars are available, their specifications and also be able to compare their prices. This makes the shopping experience for different consumers very enjoyable and comfortable as the customers are able to get all the required information within a very short period of time (Gray, 1992).
The information system is set to have several benefits and a wider range of people involved in the day to day operation of the business are to benefit. However there are some problems that are likely to encounter the implementation process. Some of the problems that are likely to affect the implementation process include: backing-up data when changing from the old system to the new system. Transferring of information from the old system can be a very big challenge as the operations of the company has to be stopped for a while as the transferring process takes place (Gray, 1992).
Objects/entities and attributes
There are several objects that are involved in the implementation of the database for the car dealers company. Some of the objects that can be used in the above database for the company dealing with cars include:
i. Managers – the managers will be coordinating all the activities taking place in the company. The attributes under the managers’ table include:
b. Manager’s name
c. Area of profession
e. Types of cars under him/her
Area of profession
Types of cars under him/her
ii. Suppliers – the suppliers form a very important part of the transaction process. They form the source of the cars for the company.
b. Number of cars handled
c. Car models handled
d. Services offered
Number of cars handled
Car models handled
iii. Customers – any business entity is incomplete without customers. Customers need to purchase the items so that the stock can be replenished. This forms the major source of all the transactions in any business setup.
b. Customers’ origin
c. Terms of payment
d. Preferred supplier/model
Terms of payment
iv. Business transactions – business transaction can also be grouped as an intangible entity in any business set up.
b. Nature of transaction
c. Person facilitating the transaction
Nature of transaction
Person facilitating the transaction
v. Cars – the company deals with cars. Cars should therefore be included in the database with all its attributes that may influence different types of transactions.
c. Engine size
d. Fuel type
e. Drive type
f. Car color
Modeling the entities
This section is going to include all the entities and the attributes that are to be used in the database design. It also includes all the relationships that involve different attributes and their corresponding entities. The following entities are used in the database design:
The managers table will have the Manager’s ID to uniquely identify the manager performing the transaction. It will also have the manager’s name and his/her profession. Different managers are assigned different departments depending on their areas of profession. Sales and marketing departments for instance are handled by managers who are sales and marketing professionals. The trend is maintained in all the other departments. Heavy and light vehicles are also assigned to different managers so as to make the process of handling the transactions easier.
In the suppliers’ table, there is a unique ID for every supplier for ease of identification. Suppliers are given a maximum number of cars that they can supply to the company depending on the customers’ response on different types of cars. They also deal with specific models of cars and offer different types of after sales services once they have supplied the cars. The services offered need to be included in the database so that the customers can be aware of the suppliers with the best deals.
In the customers’ table, they must be uniquely identified using the customer ID key. Their origin should also be included so that it can be easy to calculate the shipping costs of the cars. The terms of payment should also be indicated so that it can be easy to calculate the overall cost of the car. Cars bought on credit tend to cost more than those bought on cash. The customers also need to indicate the supplier they prefer and the model of car that they intend to buy.
iv. Business transaction
Business transactions can be grouped as part of the intangible entities. The transactions should have a unique ID to identify each one of them and at the same time the nature of transaction should also be indicated. The person facilitating the transaction should also be indicated and this one involves the managers, supplier or the client.
The cars forms one of the entities with one of the biggest number of attributes as it is the main item being traded on. Each car has a unique ID which must be well indicated and the model of the car should also be indicated. In the table, the engine size, color, fuel type and the transmission should also be indicated so that the customers can have a full knowledge of the type of car that they are buying.
Relationships existing between the entities used in the database
Managers and suppliers (many too many)
The managers have to contact the suppliers about the stock. They inform the suppliers to supply more whenever the stock gets depleted. The suppliers also contact the managers in order to make inquiries from time to time about the status of the stock (Teorey, Lightstone and Nadeau, 2005).
Customers and suppliers (one to one)
Both the customers and the suppliers also have a constant communication on the status of the cars that they want or supplying respectively. The communication through this channel is a two way type of communication. Customers only inquire about the models supplied by the suppliers while the suppliers are only interested in knowing the requirements of the customers.
The managers facilitate the transactions between the suppliers and the customers. The customers and the suppliers do not engage in a direct type of business transaction.
Managers and cars (one too many) – this is because there are several attributes of the cars that the managers may be interested in.
Suppliers and cars (one too many) – the suppliers may be interested in providing several attributes about the cars that they are dealing with.
Part B – Interface Design:
The design being implemented is user friendly and the users can easily interact with the system without much hustle (Date, 2003). The system has three sets of interfaces that can be used by different sets of users. The first interface is between the managers and the system, the second interface is between the customers and the system while the third interface is between suppliers and the system. There is no direct interaction between the customers and the suppliers. The customers need to contact the company via the system so that they can be able to get information about different types of cars that are available, their specifications and their corresponding prices. The managers then make an inquiry to the suppliers so as to give them information about different offers available. The offers are then communicated to the customers through the system (Kroenk, 2007).
General overview of the system
Some of the principles that the system takes into consideration include:
There is no direct relationship between the customers and the suppliers. Therefore it is not possible for a person who is on the supplier side to view any person on the customers’ side. Both the suppliers and the customers can not be able to view each other. However a person in the company server machine can be able to view both the clients and the customers. This makes it easy for the company to control and moderate all the transactions related to the sale of cars (Kroenk, 2003).
There are some constraints that need to be put on different components of the system so as to ensure information integrity and authentication. A person on the supplier side can only be able to view data about the manufacturer but not able to make any change/ modification to that type of data. Clients also on the other side are only given read privileges to information on the company’s side (Kroenk, 2007). They are also given privileges to view and modify data on their side. This helps in ensuring that the information available on the company’s side is authentic and free from any kind of modification from the supplier’s side or from the customer’s side.
There is a two way communication channel available to each communication media. The clients can communicate with the managers and the managers can communicate back. The suppliers can also contact the managers about the situation in the company; the managers can also communicate back. The company also offers a communication forum whereby the clients can give their feedbacks or comments regarding the services they get from the company. This can help the company improve the nature of services offered hence improving their profitability (Kroenk, 2007).
Whenever a supplier updates some given information on their side, the information should also be updated simultaneously on the company’s side. This helps in ensuring that the available information is always consistent and not subject to any kind of miscommunication (Kroenk, 2007).
The database being implemented is very simple and has only three sets of interfaces. The simplicity nature of the database makes it more affordable s it employs few design features and also at the same time, it is easy to train the users on how to use it. This can help the management spend fewer resources in the overall implementation phase thus making it more affordable (Lightstone, Teorey and Nadeau, 2007).
Usability principles used by the system
This system is very effective as the information entered from one end of the system is captured and then reflected on the other side. This makes it possible for the system to be used in handling database features in real time. The consistency nature of the database makes it very effective in handling different functionalities of the system.
Information entered by one user in one end is reflected to the other end in real time. This makes the database very efficient in handling different functions. The management no longer needs to handle things manually as it used to be the case but the improved database system helps in automating all the operations thus improving the efficiency of the company.
The consistency nature of the system helps in ensuring that transactions are replicated and that nothing goes wrong in the course of the operation of the system. This helps in making the system more secure and information security guaranteed.
Part C: Information Analysis
The main intention of designing this database is to show the different categories of vehicles available in the company and also establish a way in which the customers can easily access the information about the vehicles and make appropriate choices before making a decision on which vehicle to buy. The database also forms an avenue for communication between the managers, customers and the suppliers. Though in some cases the communication may not be direct, the database facilitates communication between different players in the industry. The database also acts as a negotiation channel between the clients and the suppliers. The customers are able to give their opinions on different services offered to them and then the management can be able to make the necessary adjustments so as to ensure maximum customer satisfaction. Customers are allowed to quote their prices and then negotiations can be done so as to come up with the best offer for different types of vehicles in the company (Gray and Reuter, 1992).
The database is divided into a hierarchal structure whereby access is limited to different provisions assigned to different managerial levels (Gray and Reuter, 1992). The senior most managers can access most of the data about the organization while the junior most staff has limited access to the database. They can only view information about their departments. In the design of thee database for the company, we used MYSQL language for designing the database to be used by the company. The data in the system can be accessed by both the junior management and the senior managed. However, the level of privileges varies depending on a person’s position in the company. The database allows for both top-down and down-top writing by the senior managers. However, as the level reduces, the privileges decrease and the users are only allowed to read data from other departments but not write anything to data that is above their hierarchal level in the company.
Language tools analysis
When designing the database, we used an UML language (Unified Modeling language) and the entity relationship diagrams (Gray and Reuter, 1992). Both of these methods for representing the database involved the use of some universally understood symbols and formats. These made it very easy for the designers of the system and other stakeholders to understand the concept being presented hence made it possible for a quick implementation of the system. During the documentation process of the system, we used both the short concise sentences and a positive language to represent the database in different formats. This made it possible for the documentation to be viewed as a formal document. The language that was used in the implementation process was the English language which was characterized by simple and short statements.
Database designing is very vital in any organization that wants to exercise a good performance and a proper control of all the activities in the company. The database will make it possible for the company to exercise a good stock management system and also be able to keep track of their customers and know which customers are more profitable to the organization and which ones are not. The new database to be designed for the company is automated and whenever a car is sold off from the company, it is automatically deleted from the database hence making it easy for the company to efficiently manage their stock. Information available in the database can give the clients a glimpse of what should be expected as far as the management of the stock and the customers is concerned. The company can also know the most profitable stock hence give a provision for more stocking of the fast moving categories of cars.
1. Development of an object-oriented DBMS, 1986; Portland, Oregon, United States; Pages: 472 – 482
2. Beynon-Davies, P.,2004. Database Systems. Palgrave, Houndmills, Basingstoke.
3. Connolly, Thomas and Carolyn Begg. 2002. Database Systems. New York: Harlow,
4. Date, C. J., 2003. An Introduction to Database Systems, Fifth Edition. Addison Wesley: New York USA
5. Gray, J. and Reuter, A.,1992. Transaction Processing: Concepts and Techniques, 1st edition, Morgan Kaufmann Publishers, New York USA
6. Kroenke, David M. and David J., 2007. Database Concepts. 3rd ed. New York: Prentice.
7. Lightstone, S.; Teorey, T.; Nadeau, T., 2007. Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more. Morgan Kaufmann Press. USA
8. Teorey, T.; Lightstone, S. and Nadeau, T. 2005 Database Modeling & Design: Logical Design, 4th edition, Morgan Kaufmann Press, USA