In this assessment, you are required to perform the following tasks for the case study given below:
CQ Advertising Agency (CQAA) is a successful advertising corporation that displays advertisements belonging to their clients. CQAA owns many prime location advertisement spots (SPOTS) at various locations in and around Sydney. These SPOTS can be seen in shopping malls, railway stations, building roof-tops and many other places. Based on the charges applicable, CQAA has classified the SPOTS into three different categories, viz., standard, prime and superior. Currently, these SPOTS are used for displaying banner like advertisements. When a new client requests for advertisement options, CQAA suggests the currently available SPOTS to them. After the selection of the SPOT by the client, CQAA enters into a contract with them to display the client's advertisement in that selected SPOT for a specified period.
The client is expected to supply and display their banners at the agreed SPOTS themselves and any damages to those banners are not the responsibility of CQAA.
CQAA collects client's details such as company name, address, contact person, phone, email address, etc. At the time of making the contract, the CQAA demands an initial payment of 40% of the total charges from the client. The remaining charges are paid by the client after receiving the final invoice from CQAA. Please note that CQAA charges the client based on the SPOT-category and contract period of advertisement.
Note:
The attributes of the possible entities have not been mentioned clearly. You are required to conduct further research and identify the necessary attributes. Please make sure that each entity has at least two relevant attributes.
You are to develop an entity-relationship model to capture the data requirements described above. For any information that is not specified or is unclear, please state a reasonable assumption and model accordingly. The following assumption is permitted for this assignment:
The charges for different SPOTS-category remain constant and do not change over time.
A.Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram for the above case study. You can use any software tool/application to draw the ERD.
B.List your assumptions and justify the relationship constraints in your ER diagram.
Your answers should be relevant to your ER diagram for the given case study only.
C.Create Logical Design
Map your Entity Relationship model into relations and make sure that all the relations are in BCNF. Provide all the relations in the following format:
Student (StudentID, StudentName, Street, Suburb, State, PostCode, Email, ContactNumber)
Unit (UnitID, UnitName)
UnitStudent (UnitID, StudentID, grade)
foreign key (UnitID) references Unit.UnitID
foreign key (StudentId) references Student.StudentID
D.Provide Physical Design
Complete a physical design for any one table. For each column in that table, specify the name of the column, type/length, key details, whether it should be required and any suitable default value. You may use the following table in this regard.
| Column name | Type/length | Key | Required | Default value |
Note: Refer to page 372 of the textbook (9th ed).
E.Implement Database
Create a Microsoft Access database. Implement all tables and relationships as per your design in the previous steps. Create all columns in each table and choose appropriate data types. All the relationships should have appropriate referential integrity and cascade options applied. You do not need to create any form, query or report. You do not need to insert any records in the tables.