CIS2002 Assignment 1 2014 Specification Description

Cis2002 Assignment 1 2014assignment 1 Specificationdescription

This assignment consists of three sections: applied database theory, SQL queries, and data modeling. You are required to attempt all sections and questions, submit the work electronically by the specified due date, and use the USQ data modelling and normalization methodology based on Clive Finkelstein’s techniques. The assignment emphasizes proper ERD creation, SQL query formulation, and relational database design according to the USQ methodology.

Paper For Above instruction

Fabulously Fashion is a ladies apparel business operating over 60 retail outlets across NSW, VIC, and QLD. The company aims to develop a comprehensive database system to manage inventory across outlets and warehouses, with a web-based interface accessible to store and warehouse managers from their offices. The project is in the requirements-gathering phase, and the focus now is on refining data needs and modeling the database following the USQ methodology rooted in Clive Finkelstein’s approach.

The assignment is divided into three main parts: applied database theory, SQL queries, and data modeling.

Section A: Applied Database Theory

In this section, students are tasked with preparing a memorandum for the project manager, outlining the expectations of three distinct user groups: data entry administrators, store managers, and warehouse managers. The memo should be no longer than 500 words and must include insights on their specific requirements and functions within the inventory management system. For instance, data entry admins may focus on input accuracy, validation, and ease of data entry; store managers may require real-time inventory levels, sales reports, and stock replenishment functionalities; warehouse managers might need detailed stock tracking, reorder levels, and shipment scheduling. The memo should synthesize these expectations based on research and practical considerations, providing workable solutions aligned with best practices.

It is essential to use relevant literature, such as Selected Reading 1.3, as a foundation for understanding user expectations and system design considerations, though formal referencing is not mandatory in this section.

Section B: SQL Queries

Students will be provided with the Car Hire database, which includes several tables such as I_CAR, I_CARGROUP, I_MODEL, I_CUSTOMER, and I_BOOKING. The database schema and sample data will be accessible on the USQ Oracle server. Using SQL, students are required to write queries solving specific data retrieval problems, each accompanied by the expected output demonstrated via screen dumps or sample result sets.

Queries include retrieving customer details, car details ordered by cost or mileage, filtering cars based on service status and availability, identifying unpaid bookings, calculating expected return dates, and more complex conditions involving multiple filters. Attention must be paid to correctness, efficiency, and clarity of the SQL statements, with partial credit awarded for well-constructed alternative solutions.

Section C: Data Modeling

This section involves constructing entity-relationship diagrams (ERDs) and relational models for four scenarios based on provided specifications:

• Ambulance drivers assigned to multiple teams over time, with details on assignment periods and driver ratings.
• Questionnaires with multiple questions, capturing questionnaire metadata and question attributes.
• Academics specializing in one area, with each area possibly associated with numerous academics.
• Researchers working on multiple research projects, with each project having a single head researcher, including details about researcher roles, start dates, and contact info.

For each scenario, you must produce a detailed ER diagram using Finkelstein’s methodology, illustrating entities, relationships, and cardinalities. Also, list all relations (tables) with attributes, primary keys, and foreign keys. The aim is to produce well-structured, comprehensive models that reflect the specifications and demonstrate understanding of normalization and relational design principles.

References

• Finkelstein, A., & others. (2010). Information systems development: Methodologies, techniques, and tools. Springer.
• Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.
• Elmasri, R., & Navathe, S. B. (2010). Fundamentals of Database Systems. Addison-Wesley.
• Ullman, J. D. (1988). Principles of Database and Knowledge-Base Systems. Computer Science Press.
• Hoffer, J. A., & others. (2016). Modern Database Management. Pearson.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Kroenke, D. M., & Boyle, R. J. (2014). Database Concepts. Pearson.
• Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems. McGraw-Hill.
• Leondes, C. T. (2015). Information Technology and Computer Security. Springer.
• Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.