Posted: November 16th, 2015

Animal Tracking Systems (ATS)

Your Tasks
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.
University of Derby Online Learning
www.derby.ac.uk/online 6 of 12
 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
Please read the following guidance…
Important Notes and FAQ’s.
 Make sure your full name and student ID are on the front page of the assessment
 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