The Following Case Study

CASE STUDY:DIGITAL

TASKS

The following Case Study

1. Derive an Entity Relationship Diagram (ERD) showing the entities of interest and associated attributes, relationships, dependencyand indicate primary and foreign keys
2. Implement the tables in MS SQL Server 2005 by draw the ERD in MS SQL Server 2005
3. Populate the tables with the sample data.
4. Provide sample SQL code to demonstrate and understanding of DDL and DML SQL statements.

You may decide to modify the data requirements or generate additional data attributes to assist in your design of the database. Write down details of any assumptions you have made that you feel are important.

You may decide to follow the ERD’s in Answer Points.

CASE STUDY:Digital Voice Systems Scenario.

Modern private digital telephone exchanges are basically complex computer systems. This provides many advantages: in particular these digital exchanges are capable of providing a continual log of their activity. Every call is logged providing information for outgoing calls such as source extension, destination number, charge band. For incoming calls the information provided is destination extension, duration of call, number of rings before answered etc.

You are an employee of a company which specialises in providing turn-key systems for vertical markets. The product design team of your company see the increasing use of such digital exchanges as an opportunity for the development of a new product. This would be a powerful management system for private digital phone systems incorporating a full function relational database management system. As part of the feasibility study you have been asked to produce an initial database design for the proposed system. The following is an extract from the background information provided by the planning team.

………… Large organisations such as financial institutions or health services, are heavily dependent on voice communications and incur large costs in this area. Currently most such organisations have private exchanges with digital equipment. Proper management information on the use of internal and external telephone lines, until now, has been largely unavailable. However, these digital systems have a facility for continuous call logging which provides the possibility of real-time or batch processing of the logging information and the processing of this information into a relational database.

The organisations we envisage being the target market would have a number of multi-building sites. These sites would be distributed around the country, each site would have one or more digital exchanges. At each site there would be a number of employees directly involved in the running of the voice systems. In particular a system would have to keep track of a telephone exchange manager for each site as well as the telephone operators.

Clearly details of many employees would need to be recorded together with the details of their associated telephone extensions. In practice employees might have more than one extension. Some employees also might share extensions and the system would have to be capable of coping with this.

One of the main functions of the system would be to allow telephone exchange operators to use a terminal for on-line directory enquiries. Access to employees' telephone extension numbers would have to be possible by name, part name or by room number. The ability to determine the employee associated with a particular extension number would also be useful. Further the system would need to be able to determine all the extensions associated with a particular room.

We can assume that most organisations would divide their employees into departments and a major function of the system would be to allow the production of frequent cost information reports on use of telephones for the various departments. The ability to classify telephone extensions by department would therefore be an important facility.

A basic assumption would be that all extension numbers within an organisation would be unique. One of the advantages of modern digital exchanges is that no extra dialing is needed to reach any extension. However, it would be important to record which exchange an extension was physically connected to as well as which rooms and buildings are served by a particular exchange.

An important function of the system, in addition to operator directory queries and call data logging, would be the production of telephone directories for the organisation. Such directories would usually be ordered by site as well as employee name and department. Only the minimum information about employees would need to be kept as the system would only be involved in identifying telephone users by name, job title, and department.

The call logging data is provided in binary format from an asynchronous port on each digital exchange. However, from the overall database system view we can ignore the problems of data communication systems and data acquisition. We can view the call logging data as simply call records. There would be two basic types of call, incoming and outgoing: the information stored about each call would differ according to call type.

For outgoing calls each record would provide source and destination telephone numbers as well as call duration. Currently it is envisaged that application programs running overnight would be used to process logging records and update the telephone usage statistics of each extension.

Such statistics would include the number of calls made in each week, month and current financial year. Average cost and duration of calls would also be stored as well as the average time for the extension to be answered. This would be particularly useful in the analysis of the performance of telephone operators. A running total of cost for each extension would also be kept. As many employees could be associated with the same extension it would be necessary that each extension would be associated with a single department for call charging. Incoming calls incur no direct cost and therefore the information stored would be extension, duration of call and the time for the call to be answered. Failed calls would not be recorded......

1

CASE STUDY:DIGITAL

CASE STUDY:DIGITAL

Answer pointers

The following model and schema does not cover every aspect of the case study.

1.

BASIC MODEL

IMPROVEMENT 1

IMPROVEMENT 2

1

CASE STUDY:DIGITAL