700011 : Database Design and Development - ER Diagrams in RDBMS - Computer Science Assignment Help

Assignment Task :

Objectives 

This assessment task focuses on the following Learning Outcomes:  

• Apply basic skills in database modelling, including ER diagrams and normalisation in RDBMS. 

• Explain the basic concepts of relational algebra and apply them in queries. 

Project Specification  

Western Advertising Agency (WAA) provides local advertising solutions to help national companies,  regional marketers and local brands gain exposure and showcase their products in the markets. With  the recent growth in the demand for digital advertisement, the top management has realised the need  for a database application to assist sales representatives with maintaining a record of advertisement  order details. The requirements collection and analysis phase of the design process has been  completed and provided the following data requirements specification for the database: 

1. WAA employs a range of staff for its day-to-day operations. Staff may be employed full-time, part time or on a casual basis. Staff details such as name, address, contact number, email, hire date, and  position (such as sales representative, media planning, ad operation etc) are recorded in the system.  Each staff member is identified by a unique number within the agency.  

2. The agency arranges mentoring program to provide training and coaching to newly appointed staffs.  The employees having more than 15 years of experience in advertising, are eligible to work as  mentors. Each eligible mentor may have at most 5 mentees. 

3. Potential advertiser can register themselves with the agency by providing details such as business  name, address, email, URL, phone number and full name of contact person. On successful  completion of the registration, each advertiser is assigned an 8-character reference number which  is unique within the agency. 

4. At WAA, registered advertisers can place advertisement order online. For each order, the system  keeps record of details such as title, date of order, placement of the advertise (such as automatic or  customised audience), format (such as text, image, banner, or video), advertise start date and end  date. The advertiser may provide an estimated budget and any special note for the advertisement. Each advertiser receives a unique number after placing an order successfully.  

5. The agency publishes advertisement on a number of platforms including search ad, Google ad, social  media, video streaming sites like YouTube etc. The system stores details of these platforms including  a unique name of the platform, a short description, URL, recommended resolution in pixels and cost  per click (CPC).

Database Design & Development 

6. On receiving the order, an agency staff processes it by determining the right platforms for the ad. An advertisement can appear on multiple platforms with varying start and end date. For each  placement of the advertisement on a platform, WMA receives a percentage of commission depending on the number of clicks on the advertisement. The staff and date of processing the order are also recorded in the system.  

7. The system generates an invoice including all advertisement publications of the advertisement order which is electronically accessible to the corresponding advertiser. Each invoice includes details  such as a unique invoice number, issue date, total amount payable, due date, and additional note (if  any). 

8. The advertisers can make a one-off payment for their invoice or they can pay in instalments. For  each payment, the system stores the payment date, payment method, payment amount and the  name of the person making the payment. The system generates a unique payment reference number for each successful payment received. 

9. The agency (WAA) maintains a portfolio for each advertiser. Each time an advertise is published by  an advertiser, the system records their preferred platform, duration, and cost of the advertisement. Naturally, no portfolio exists in case the advertiser has never advertised with the agency. 

10. At the core, WAA relies on delivering high quality customer service. Therefore, the advertisers are  asked to share their experience of advertisement with WAA. For each advertisement order, the  advertiser may write multiple reviews. The system records each review including review date, overall  rating, and general comments.  

Task Specification 

1. Draw a conceptual data model (ERD) using either crow’s feet or UML notation that  models the data requirement for the Western Advertising Agency database. Your  diagram should include the following:  

a. all entities, relationships (including names) and attributes; 

b. primary key (underlined); 

c. cardinality and participation (optional / mandatory) symbols; and  

d. assumptions you have made, e.g. how you arrived at the cardinality / participation for  those not mentioned or clear in the business description, etc.  

2. Create relational data structures that translates your conceptual data model into logical  data model which includes: 

3 Database Design & Development 

a. relation names;  

b. attribute names;  

c. primary keys and foreign keys identified; 

3. For each relation in task 2, identify a list of functional dependencies and the level of  Normalisation achieved.  

4. Explain the basic concepts of relational algebra and apply them in queries. 

a. Based on your relational schema, write SQL queries that will produce the following data: 

i. Details including name, email, and contact number of all sales representatives  working for Western Advertising Agency. 

ii. Details including title and date of order of all advertisements that are going to be  published in the month of January 2021 and have an estimated budget of $2000  or more.  

b. Use basic operations of Relational Algebra such as projection, selection to express the  above queries 

This Computer Scieince Assignment has been solved by our Computer Scieince Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.