Topic : Advanced database development
Words required: 2000

1. Optimize your Database (10 Marks) You should now optimize your database: 

• Apply suitable database optimization techniques to your final set of tables.
• Aim to implement a range of indexes. 
• Run a suite of queries that will invoke those indexes. 
• Aim to demonstrate some index suppression techniques. 
2. Secure your Database (10 Marks) Your optimized database should now be secured. To demonstrate this: 
• Create three new users on your database 
• Allocate them different security privileges and roles. 
• Create a suite of views over your existing base tables. 
• Issue appropriate privileges so that at least one user can use those views. 
• Clearly demonstrate what actions each user can/cannot perform. 
3. Building a Web Interface (15 Marks) You now have a fully working, secure and optimized database. Your final task is to design and build a web-based interface to that database such that it can support the following operations: 
• Insert new rows of data 
• Update existing rows of data Delete existing rows of data 
• Query existing rows of data Clearly label all tasks and take care to explain and discuss your technical work 
• For the modelling and design work you must supply clearly annotated diagrams 
• For the SQL work you must supply full code listings of the inputted code and screenshots of the outputted results – even if no rows were returned. Each screenshot must include some identifying feature – such as a username or user ID – that proves beyond doubt that it is your own work. 
• If you need to research, then fully reference all such sources using the Harvard notation.
4. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15 marks) Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show all intermediate steps
Boyce-Codd Normal Form (BCNF), Database Security and user web interface
can writer normalize my ER model to BCNF

Title: Animal Tracking Systems (ATS) This is an individual assessment worth 50% of the module. Case Study Animal Tracking Systems (ATS) is a developer and supplier of specialist software products to individuals, businesses and government organizations working in the wildlife and veterinary sectors. Their software allows individual animals and birds to be remotely monitored and tracked. Examples include anti-poaching work in Africa, bird migration pattern analysis across Europe, canine tracking by law enforcement and the military or simply a private individual attaching a chip to a pet cat’s collar to avoid it getting lost. As part of their operations and after-sales package, ATS provides a help desk for clients who have questions about software purchased from the company. The range of software is extensive and offers a wide range of functionality at various price points. Some functions offered include location identification, migration and travel pattern analysis, health data capture, eating and drinking data capture, sleeping and resting pattern analysis and so on. When a call comes in, an operator enquires about the nature of the call. For calls that are not truly help desk functions, the operator redirects the call to another unit of the company (such as Order Processing or Billing). Since many customer questions require in-depth knowledge of a product, help desk consultants are organized by product. The operator directs the call to a consultant skilled on the software that the caller needs help with. Since a consultant is not always immediately available, some calls must be put into a queue for the next available consultant. Once a consultant answers the call, he determines iif this is the first call from this customer about this problem. If so, he creates a new call report to keep track of all information about the problem. If not, he asks the customer for a call report number, and retrieves the open call report to determine the status of the inquiry. If the caller does not know the call report number, the consultant collects other identifying information such as the caller's name, the software involved, or the name of the consultant who has handled the previous calls on the problem in order to conduct a search for the appropriate call report. If a resolution of the customer's problem has been found, the consultant informs the client what that resolution is, indicates on the report that the customer has been notified, and closes the report. If resolution has not been discovered, the consultant finds out if the consultant handling this problem is on duty. If so, he transfers the call to the other consultant (or puts the call into the queue of calls waiting to be handled by that consultant). Once the proper consultant receives the call, he records any new details the customer may have.

For continuing problems and for new call reports, the consultant tries to discover an answer to the problem by using the relevant software and looking up information in reference manuals. If he can now resolve the problem, he tells the customer how to deal with the problem, and closes the call report. Otherwise, the consultant files the report for continued research and tells the customer that someone at ATS will get back to him, or if the customer discovers new information about the problem, to call back identifying the problem with a specified call report number. A database is required to manage the data for the above scenario. It should also be possible to query the data and produce various reports for management as and when required. Your job is to deliver that database by correctly identifying what data must be captured and how it is related. NOTE: The above case study is simply an outline of the company and you will need to make your own assumptions and interpret or even extend the scenario as you go. Use your imagination as you see fit but you must clearly document all assumptions and extensions.

Your Tasks 1.

 1. Produce an Entity-Relationship Model for the scenario described above (15 marks) Develop a top-down design of the data in the form of an entity-relationship diagram. You should note all assumptions you make about the data and the reasoning behind your design choices. Also include any appropriate constraints and a list of entity types showing their attributes and identifiers.

 2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF). (15 marks) Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show all intermediate steps.

3. Implement your final database design. (15 marks)

Take each of the relations from your relational model and implement them as SQL tables. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate. Then, using appropriate sample data and your own imagination, populate your finished tables.

4. Query your database. (20 marks) Using SQL, write a set of realistic sample queries based on the above scenario (use your imagination for details of each query) but they should include the following SQL query techniques:

· Joins (using two, three or more tables)

· Set operations (UNION, INTERSECT and MINUS)

· Ordering · Grouping

· Aggregate functions (MIN, MAX, AVG, COUNT, SUM)

· Table aliases · Renaming columns · Sub-queries (nested queries)

You should aim to write at least ten sample queries – ranging from basic SELECT…FROM…WHERE queries to more advanced ones using the above techniques.

5. Optimize your Database (10 Marks) You should now optimize your database:

· Apply suitable database optimization techniques to your final set of tables.

· Aim to implement a range of indexes. · Run a suite of queries that will invoke those indexes.

· Aim to demonstrate some index suppression techniques.

6. Secure your Database (10 Marks) Your optimized database should now be secured. To demonstrate this:

· Create three new users on your database

· Allocate them different security privileges and roles.

 · Create a suite of views over your existing base tables.

· Issue appropriate privileges so that at least one user can use those views.

· Clearly demonstrate what actions each user can/cannot perform.

 7. Building a Web Interface (15 Marks)

You now have a fully working, secure and optimized database. Your final task is to design and build a web-based interface to that database such that it can support the following operations:

 · Insert new rows of data

 · Update existing rows of data

Delete existing rows of data

 · Query existing rows of data

Clearly label all tasks and take care to explain and discuss your technical work

· For the modelling and design work you must supply clearly annotated diagrams

· For the SQL work you must supply full code listings of the inputted code and screenshots of the outputted results – even if no rows were returned. Each screenshot must include some identifying feature – such as a username or user ID – that proves beyond doubt that it is your own work.

 · If you need to research, then fully reference all such sources using the Harvard notation.