M30208 Database Management - University of Portsmouth

Select one of the topics listed below.

A maximum of 5 students can attempt each topic; you will be prompted for a choice of topic during one of the workshop sessions.

The report should contain around 1500 words. Please ensure it is properly referenced and you should follow the guidance given on the University library web page on referencing and citation. Make sure you adhere referencing style adopted by the University called APA Havard 7th Edition, this must be used in your work.

1 Database Design - investigation of alternative approaches; discussion of advanced techniques and how they improve the design of a database.
2 SQL: Review of SQL concepts; how SQL implements the relational model; advantages and disadvantages of the language.
3 SQL: Advanced features - investigation of latest features in the current SQL Standard, such as multimedia support.
4 Database container deployment and management: Feature and benefits
5 Quantum Computing impact on Databases: Discuss
6 Data migration and data security are major issues in System innovation: Discuss
7 Multitenancy Vulnerabilities and its security challenges in cloud
8 Multitenancy Architecture in Database: Issues and Challenges?
9 Data Warehousing - issues in design, e.g., star and snowflake schemas
10 Knowledge discovery in large databases: Discuss
11 An Elastic cloud -based data warehouse: Features and benefits
12 Data Quality Management Issues - discussion of how to ensure integrity and validation.
13 Document record and Content Management issues.
14 Relational model: main features; how it is supported by SQL; problems of areas not supported.
15 Investigation of Application Development tools and how they aid the development of a database application, such as SQL Developer, Apex
16 Multi-user issues: currency control; handling a multi-user DBMS; SQL support
17 With a real life example, differentiate between Data Masking and Data Cleansing, how these would contribute to data management and operational efficiencies.
18 Modernizing Data Architecture for a Digital Age Using Data Virtualization

Assessment 2

You are required to develop a database solution for the Folly Bee Pubs.
CASE BRIEF: Folly Bee Pubs
Folly Bee Public Houses are venues that provide beverages and food in many high street locations throughout the UK. They own approximately 60 buildings in the most popular areas of large cities in the UK. Each venue sells both drinks (alcoholic and non- alcoholic) as well as having a reasonably broad food menu. They provide breakfasts, lunches and dinners.
They are exceedingly busy due to their competitive pricing.
They require a new database management system to replace the original system they had in place as this is no longer viable for the amount of data they are storing. They would like a design for a centralised database that allows web interface access for the employees.
The database keeps records of all the sales of food and drink, stock control for food and drink and employee shifts to show who is on duty at what time.
Their menus are a Breakfast menu, a Lunch menu and a Dinner menu all with several options on each. These vary in price, so there is a small breakfast for £3.10 and a Large Breakfast for £5.00 or Toast with Tea or Coffee for £2.50.
Lunches are dishes such as Lasagne £5.50, Fish and Chips £6.00 etc. and the dinner menu prices are slightly higher with, for example, Burger and a Pint for £7.50 or a Steak at £10.
These are just an example of what is on the menus. The menus may change over time but they are not expecting them to change every week. The menu and prices are the same in every location.

The drinks are the usual range of alcoholic drinks such as beer, lager and spirits. Non-alcoholic drinks such as orange juice and hot drinks such as tea and coffee.

Folly Bee needs to capture the sale of all of these items. They use this data for stock control and to place large bulk orders for food and drink. Additionally, they use it for Business Intelligence showing them when their busy times are, how many repeat customers they get, comparison of sales across venues and to plan for staffing.

In every location there are Venue Managers, Bar Managers, Bar Staff, Waiters, Kitchen Porters, Chefs and Cleaners employed. The size of the venue will determine the number of staff employed in the venue. The three main staff are the venue manager, the bar manager and the chef. These are the only staff on a full time, permanent contract. All other staff are part time and have casual contracts. Even though they are a big chain and looking for efficiencies they do not believe in zero-hour contracts so all staff are paid for a minimum of 10 hours per week.

The sales are captured through electronic tills and customers can pay by cash or card.

You are a junior database administrator (DBA) working for a Systems Development company and have been in post approximately 9 months. During this time, you have been working on existing database systems that have been developed by the Company's Senior DBA. (You can assume that Olumuyiwa and the teaching team would have this role.)

One of the Company's clients has asked for a system to be developed for them (see attached case study). The Senior DBA does not have the time to devote to this project yet and so has asked you to work out the design, development and justification. Therefore, the work you produce is for a Senior DBA - to save them thinking time when starting the project - and as such you can assume they understand more about databases than you do. Your report should take this into account and you should not describe or explain topics that the Senior DBA already knows but you should analyse their importance and relevance in the given scenario.

Task T1: EERD
Using the information given in the case study, draw an Enhanced Entity-Relationship Diagram for the database system. Do NOT include any attributes.
The EERD should fit on ONE page and MUST be produced electronically. Hand-drawn diagrams will receive NO marks.
There are a number of free or trial-period EERD drawing software packages that you can use, Like Lucidchart which is free to create an account with your University email.

Task T2: Rationale and Assumptions
Clearly state any assumptions you have made about the system. Assumptions are made when the case study text does not give you enough information. These assumptions should only relate to the design of the system. For example, you may have decided to include or omit certain entities, or you may have decided to generalise rather than specialize some entities - tell us why you did this. (ca 200 words)

Task T3: Data Dictionary/ Scripts
Create a data dictionary for the tables you have identified in your EERD. Don't forget the intersection tables for any many-to-many relationships. The tables should contain a logical range of attributes and clearly indicate the keys. All tables should be in 3NF.
For each table, the data dictionary should include the following:
• The attributes for the table, as they will be named in the database
• The data type and size you have chosen for each attribute
• Where appropriate, whether an attribute is a Primary Key or could be an Alternate Key
• For Foreign Keys, the table they relate to
You must use the template we have provided for you at the end of this document to complete this task.

Task T4: SQL Queries/ Final Demonstration
Create and populate a working database for the given case study. You should take on board the feedback from us given for all exercises and class examples on EERD and data dictionary when creating the required tables.
Write FIVE SQL statements that reflect the needs of the business. These statements should include some of the more complex SQL syntax you have learned this year.
Your tutor will sit with you, execute and assess the queries, ask questions about how you have developed the tables, why you have chosen the keys you have etc.
The marking will be for:
• The level of complexity of each query,
• The query being of use to the business,
• Good input and output formatting,
• Good design (relating back to the EERD & Data Dictionary),
• Use of any feedback you have received about your design,
• Use of additional features such as constraints etc.,
• Good differentiation among your queries
You should come to the demo with a printout of the tables, data and the queries you are running, and with some notes regarding your reflections on the unit and your work.

Attachment:- Database Management.rar