Learning outcomes
1. Understand the database concepts and components
2 Develop a logical database design.
3 Implement a physical database for a given requirement
4 Test and document databases
Scenario and the Task
The Institute of PG Technology has the biggest library in Newplymouth. Currently it has about 200 members. Currently all the functions of the library are done manually. They have plan to expand the library by adding new sections so now management has planned to automate the library management system, in order to provide its members better services to reduce the work load of the staff.
Assume that you are the database engineer of Creative Vision information technology (IT) services, company providing IT solutions worldwide. Now they are developing automated library system for a The Institute of PG Technology. As a data base engineer your responsibility is to design and implement a robust Database System for above mentioned library.
Library has different units such as computing, management, English etc. There are number of books under different categories. There are number of book copies for one book (book title).Any person who joins The Institute of PG Technologycan become a member buy paying member fee.
After doing the fact finding part development team has identified that below mentioned information's to be stored in the system.
Author's information such as author id, first name last name, contact information, and publishers information Pub id, Name, Street, City. Unit is identified by unit id, unit description is stored in the system. Category id and name, Book title should contains Call number, Title, Price, and Availability (y or n) as its attributes. Member should have Member id, Name, City, Member type, Member date (date of membership), Expiry date. Member can borrow book copy issue date and return date should be mentioned for specific member. Member can reserve book title request date and ref number is mentioned for that member. Book copy contains access id and edition
Students can add any functionality which will enhance the system and make the proposed solution more comprehensive.
Use Harvard referencing to properly acknowledge all the external sources you use.
Tasks:
Design the database with the constraint that the available technology is relational.
1. Explain what is data model, Critically compare different data models and explain why older data models are being replaced by new data models.
2. Critically explain the benefits and limitations of different database technologies.
3. Analyze and briefly explain the different approaches to database design.
4. Draw an entity relationship diagram for given scenario with proper standards. Identify important keys and represent different types of attributes and relationships.
5. Draw Relational Schemas. Effectively map conceptual data models with relational database schema according to the mapping algorithm. All the steps should be clearly mentioned.
6. Create the database using SQL server. Practical submission and Demonstration
7. Write 10 sql queries for given requirements using SELECT Statements. Practical submission and Demonstration
8. Generate the suitable management reports for database by using graphical format.
9. Explain the purpose and benefits of having queries in the database for construction firms' decision making process with the examples.
10. Provide the Test plan, test strategy and proper test cases and provide all the test documents.
11. Create the user manual for the database.
12. Explain how verification and validation has addressed in your data base and the access rights to various types of users.
13. Provide a well-structured documentation including proper academic style and Referencing and in-text citation using Harvard Referencing System.