Database Design and Administration

Learning Outcomes

On successful completion of this course, students will be able to:

1. Explain and evaluate the use of data modelling in information systems development.
2. Implement a suitable database development methodology.
3. Correctly use Structured Query Language (SQL) in the development of a relational database.
4. Outline the fundamental principles of effective data management within an organisation.

Course Requirements

Requirements for Successful Completion of this course:

We will discuss together our rules for how the class will operate but there are three things that are vital if you wish to succeed on this course:
- Attendance
- Participation
- Working on your course throughout the semester, not just at the end.

Assessment Details

1. JOURNAL
In addition to working on your projects you are expected to write a reflective journal on the work you are doing for the course, in which you are to write about everything you have studied and all aspects of work for the course. Your tutor will review your journal for assessment purposes throughout the course. "Final hand in" is also required.

2. PROJECT ONE
This course takes a project centred approach to work. Throughout the course we will be working on a single project

The project is divided into three milestones. At each milestone you hand in and present all the work you undertook towards that milestone. At each milestone you hand in and present all the work you undertook towards that milestone. For the first two milestones we provide feedback about your work on the material covered up to the milestone. Marks given after each milestone will be final marks. The final milestone goes beyond but includes all work you have undertaken in the project.

You are encouraged to work and study together, however your individual project work must be your own. In addition to working on your project you are expected to write a reflective journal on the work you are doing for the course, in which you are expected to write about everything you have studied and all aspects of your work on the course. Your tutor will review your journal about once a fortnight. The project brief and details can be found at the end of this course outline.

Practise Project: Brief, Resources and details. Due 6th of April

You are the database team of a small software development company that has been asked to design a multi user live/real time 2D "point-and-click" map based game as follows:
1. Characters move around the map, collecting items, competing with other characters.
2. To play the game, people must first register using an email address, and a password.
3. Each registered player chooses a game character. The characters must have at least four and no more than six attributes that can be selected.
4. Once logged in players, play against all other players through their game character.
5. When a player leaves the game their character retains its current state.
6. When the player returns to the game the player's game character is where it was left on the map.
7. Characters accumulate assets that may be used in the game play.
8. As the character moves, the position of the character is to be stored in the database.
9. As game assets move, the position of the asset is to be stored in the database.
10. Characters can communicate with other characters through text chat
11. Players can "delete" their accounts.
12. An Administrator can:
a. list all registered players
b. edit player information
c. edit character information
d. delete a player
e. delete a character

Make up a name for your game and write a brief description of your game. Then draw the game play in a series of storyboards.
Undertake:
1. Conceptual database design for the game as described in Milestone One below.
2. Logical database design for the game as described in Milestone Two below.

Marking in this project will be based on both the completion of technical systems and a clear explanation of your reasons for the design choices you made. Marks given are based on technical expertise and appropriate rationale as assessed by your tutor.

For the practise project you are to hand in one report written by the team. The report will be based on your group work to be presented during week seven of the course. You will also present your solution in team presentations during week seven.

Milestone 1. Conceptual database design

For milestone one, write a single report that describes conceptual modelling and a conceptual model
for the case study database. The report is to include the rationale for the choices you made as you undertook to produce and present a conceptual model of the database from the case study.

Include the following in your report:
0. A description of conceptual modelling. An introduction and description of the components of a Chen Entity-Relationship Diagram(ERD).

1. A conceptual ER model using Chen ERD notation of the database as derived from the case study. Accompany this with your rationale; describe and explain the reasoning and purpose of all parts of the model.
2. A data dictionary the following:
 Entities (e.g. name description, aliases, occurrences)
 Relationships (e.g. name, multiplicity)
 Attributes (e.g. name, description, domain, aliases, composite, derived, nulls, key, default value).
Accompany this with your rationale; describe and explain the reasoning and purpose of all parts of the model.

3. Discuss the assumptions made about the business rules and the reasons for the choices you made.

4. Report Details. Hand in on the date specified on the course schedule. Write ONE report that shows all the work you did for 1-3 above. Make sure you also include
- Your ER model
- Data dictionary
- Your discussion document

Milestone 2. Logical database design

For milestone two, write a single report that describes the transformation from a conceptual to a logical model for the case study database. The report is to include the rationale for the choices you made as you undertook to produce and present a logical model of the database from the case study. The report is to include all working documents as you undertake the following:

Logical database design

Produce a logical database design for a relational database to support the situation outlined in the Case Study (aka Project)that is to follow on from your conceptual design.

1. Steps
To create your relational model, you will need to follow steps as outlined in the Database development methodology supplied in class:
- Describe how to transform a conceptual model based on CHEN ERDs to a logical model. Create a list of "mapping rules".
- Create an ERD using an appropriate notation. Accompany this with your rationale; describe and explain the reasoning and purpose of all parts of the model. This is to demonstrate how you applied the "mapping rules" from the Conceptual to the Logical Model.
- Make sure your relations are fully normalised (to 3NF, consider Boyce Codd and 4th Normal forms). Document your relations. Include a description of normalisation to 4th Normal form.
- Document your logical model in a data dictionary.
? Entities (e.g. name, start volume, growth)
? Attributes (e.g. name, description, data type, length, value range, validation rules, default value, nulls, keys, entity references, integrity constraints)

2. Check your design using a NaLER Analysis(not required during "practice"project )
Carry out a NaLER analysis on the ERD. Make any changes to your logical model.

3. Report Details (Hand in on the date specified on the course schedule.)
Write ONE report that shows your work in detail as you did "1. Steps", "2. Check your design using a NaLER Analysis". Make sure you include
? A logical diagram, with a clear explanation of the purpose of the relations.
? Documentation of your relations. This must be in in tabular form as given in class.
? A copy of the NaLER analysis carried out by you.

Milestone 3. Physical database design and implementation

Write a single report that integrates reports from the previous milestones into one report. IN addition your final report is to include the rationale for the choices you made as you undertook to produce and present the physical design and implementation of the database from the case study.

This milestone follows on from your design work on the project. You are not, however, locked into the details of the design that you produced at earlier milestones. You are free to revise any or all of your design decisions based on your learning since then.

By this milestone we complete the partial development of a database , to :
1. Recognise the place of Data Modelling in information systems development, and understand a database development process
2. Design logical relational databases and convert the logical design to a physical design
3. Use Structured Query Language (SQL) to implement and use a database.
You are required to implement a database that will support the requirements in the case study.

Your Tasks

Produce a physical design of the case study database. This requires you to carry out the physical design steps of the database design methodology covered in class.

Figure 1. Physical Database Design

As well as your design you are to undertake and provide evidence of the following. Use this checklist for this milestone:

1. Build the database in SQL Server using DDL statements. Hand in DDL for building the database in SQL Server.

2. Generate and load appropriate test data. There should be a minimum of 100 rows across all the tables and no table should contain less than 5 rows. Provide a list of data in your tables. Please provide the appropriate DML statements for this step in the form of INSERT statements.

3. Write and run a set of SQL queries required to provide the required information for the project case study. These will be resolved during class discussions, but should be determined from the case study documentation. Provide the SQL queries and their results. Include a brief description of the purpose of each query.

REQUIRED PROCESSES/TRANSACTIONS

a. A sales person subscribes a new standard subscription to a BTDrone . The transaction receives the sales person Id, a discount %, all subscriber details, and a BTDrone ID.

b. For each sales person list the subscribers they have sold a subscription to. The transaction receives the sales person's name as input, and presents each subscriber's name, address, and the % they were discounted.

c. List the location in latitude, longitude coordinates, of each BTDrone that is currently in a contract. The transaction presents the Contracting organisation's name, a BTDrone ID, a Latitude, and a Longitude.

d. For a contract list all the data collected. The transaction receives the contracting organisation's name and presents for each collected data record, the contracting organisation's name, a BTDrone ID, Temperature, Humidity and Ambient light strength.

e. For each BTDrone present the list of subscribers who are viewing a live 3D video stream. The transaction lists BTDroneID, Subscriber Name, Stream ID.

f. For a given BTDrone list all the suppliers of parts. The transaction receives the BTDrone ID, and presents the Supplier Name and, Part Name.

g. Update the location and region of a BTDrone. The transaction receives the BTDrone ID, a location and a region expressed as a list of coordinates in latitude, longitude pairs. It updates the location of the BTDrone and its corresponding region. (This transaction may require more than one update query.)

h. Delete the data collected for a given Contract. The transaction receives a Contract ID, the data collected for a Contract is deleted.

i. Insert data from a BTDrone to its stored data. The transaction receives the BTDrone ID.

4. Document any significant problem(s) you encountered and the approach you took to solving it/them. This should show you made a genuine effort to solve these problem(s) and the learning you achieved from that process.

Attachment:- Project Details.rar