Library Database Design - Report Example

Database Design Student’s Name Course Title: Date: Introduction The university library has many students who borrow different kinds of books and since there are too many students to automation the process of book control like checking the authorization to use library, confirming students ID, maintaining a record of all activities between the students and the library and the issue of enrollment forms for each student. Problem Analysis Since there are too many students with keeping track of all the books made – like for example, the date on which the book was borrowed, the student involved. The proposed database design should address all these issues for successful implementation of the database system. The system foes with the following assumption for system requirements at its disposal for successful implementation of the database system: A server that runs the MYSQL RDBMS server application. Client systems with MYSQL client query tool to log on to the database and initiate queries and generate reports. Query Transactions For example, let us assume for the first entity reserve the following field lengths: book_Code (field type: alphanumeric, length: 8), book_Description (field type: character, length: 30), Price (field type: numeric, length: 5), Quantity_In_library (field type: numeric, length: 5). Queries FUNCTION GetstudInfo ( Rtype_id IN VARCHAR2 , Sem_id IN VARCHAR2) reserve_period IN VARCHAR2) RETURN BOOLEAN IS -- DECLARE authorised_type IS SELECT mem_id , sem_id , Rtype_id FROM CRS_id , course_offer_actuals ba WHERE course_offer =Enrolment Mem_id AND Enrolment CRS_id = Course offer_id AND Authorised R_id = Borrow id AND sem_id = borrow sem_id; -- BEGIN FOR authorised IN Borrow LOOP IF authorised_type.Rtype_id = reserve THEN ... ELSE ... END IF; END LOOP; RETURN v_ok; EXCEPTIONS WHEN OTHERS THEN RETURN FALSE; END; Entity – Relationship Diagram (E – R Diagram) In the Entity – Relationship diagram for this problem, there are three entities and there are only two types of relationships between the entities, they are, many-to-many type of relationship and one-to-one type of relationship. The entities are authorization_ entity, course enrolment entity and reserve/borrow Details entity. The authorization_ entity has a many-to-many relationship with the course entity because student can reserve many books and many students may borrow one type of book. The book in library entity also has a many-to-many relationship with the student_Details entity because many booked may be at one time. After normalizing the data we get three tables for the three entities and their attributes are listed as columns in each table with primary to identify each item in the table and foreign keys to cross-reference one table with another. A double-edged arrow indicates a many-to-many relationship and a straight-line indicates a one-to-one relationship in the following diagram: Verify that a design meets user requirements and provide justification of the database design. The E-R diagram above follows the top-down approach and it is follows a logical schema of designing a database. By using database normalization I have verified that the design meets user requirements. E-R diagrams are widely used in software engineering to determine the most appropriate design for database in a relational database system. Database normalization ensures that there are no duplicate fields in the database and only the relevant details are stored in a database. Normalization of students’ Database Normalization is done to organize data in a database in an efficient manner. The goal of normalization is to eliminate redundant or duplicate data and to make sure that only related data is stored in a table. There are three entities in the database; they are student- ID, library_Details entity and book _Details entity. A normal form is a set of guidelines to normalize a database and the normal forms range from the first normal form (1NF) to the fifth normal form (5NF). First Normal Form There are seven entities in the database; they are reserve, authorization type, course offer, enrollment details, semester details, authorization and borrow entities. The first three entities have been normalized already now you need to normalize the remaining three entities in this phase. In the first normal form for student_details, group together all related information for this entity. The related data are books code (alphanumeric), books name (character) and author (character). In this dataset for student_details, only the book code column will contain unique data, all other column will have repeating data. Therefore, we set the book Code as the primary key for this table. There are no further columns with a unique set of data; therefore, there are no secondary keys for this table. For the students_book_details entity we group the following sets of related data, reserve id(field type: alphanumeric), book description (field type: character), authorization (field type: numeric), authorisation status (field type: character). The only unique column in this entity is the student id column; all other columns will have repeating data. Therefore, the student_book_id is the primary key for this table. For the borrow entity we group the following sets of related data, R id (field type: alphanumeric), sem_id (field type: character) and Borrow_period (field type: numeric). The only unique column in this entity is the returns inwards id column; all other columns will have repeating data. Therefore, the borrow id is the primary key for this table. Second Normal Form In the second normal form, among the three tables to be normalized you have to identify the dependencies. In the case of student library database, the student_borrow_details table is dependent on the books_In_library table to determine the availability of books and the authorization for retrieving students’ details. Therefore, insert the primary keys of the books_In_library table and the students_details table into the student_book_details table as foreign keys. Now, the primary key for the students_books_details table is a combination of the primary key of this table and the foreign keys. Third Normal Form Remove columns that are independent of the primary key. In this assignment the book_Description column and the book_borrwed columns in semester_Details are independent of the primary key, remove those column and the database is normalized up to the third normal form (3NF). The following diagram shows the 3NF-normalized database. Shortcomings/bugs if any: None. Conclusions and Comments A thorough understanding of database structures was achieved. I learnt how to identity entities and the relationship between entities, and also to identify the attributes for each entity. Also, I learnt how to normalize the database structures, identify primary keys and how to establish relationships between tables through the use of foreign keys. I also learnt how to design an E-R diagram and also got a better understanding about the purposes of these diagrams during the coursework. Implementation The documentation provided above supports the implementation of the database. Through the process of normalization, the verification of data has been carried out and only relevant fields are incorporated in the tables, there are no duplication of fields in the database and no duplicate keys. The validation process has been taken care of through restriction on the type of data entered. For example, in students name only characters can be entered when you specify the field type as character. To prevent the field from showing null entries specify the command ‘NOT NULL’ at the end of a query for adding fields to a table. Evaluation of testing techniques and application of one of the testing techniques to database design. Many testing techniques can be applied on a database design to uncover errors if any. The different testing techniques are unit testing, black-box testing, database re-factoring etc. In this assignment an evaluation of the unit testing and database re-factoring technique is done. Unit testing technique: This testing technique is used in database design and provides a structured and automated way of testing each part of a database system. Mostly developers who develop a component do the unit tests and would write the test cases for the component. The testing of these components in an isolated manner to ensure component behavior before integrating with the main system is known as unit testing. Unit tests can be run on database stored procedures, functions and triggers. The different types of unit tests are: Feature tests: Testing the core features of the database from the database user’s perspective is known as a feature test. Schema test: This test is used to test the database schema. Testing a view to see if it returns the expected results etc are examples of a schema test. Security test: Testing the security of the database is done in this phase. Here you test to see if unauthorized users gain entry to the database by testing the database using negative tests. Data test: Data that is used infrequently is known as enrolment data and many databases have these seed data. This test ensures that your seed data exists in your database The unit testing technique is a very good technique to test your database design. This testing technique completely tests all the components of a database and is more advantageous than manual testing. Database Re-factoring technique: This is a technique in which a simple change to a schema improves the database design but retains the behavior and semantics of a database. The purpose of database re-factoring is to: Safely fix existing legacy databases: Use database re-factoring to improve data and the quality of data in legacy databases using this technique. This is the only technique that can be used to improve legacy databases. Support evolutionary development: Most of today’s development processes support evolutionary programming and therefore database design also must evolve. Database re-factoring techniques help database designs to evolve. On application the unit testing technique on the designed database, the following screens show how it was done and whether my database design passed the test. First, add the test in the unit testing application: Name the test as: Add a row count condition Set the number of rows to test to 15. Run the test And the database passed the test. References: Semantic data modeling" In: Metaclasses and Their Application. Book Series Lecture Notes in Computer Science. Publisher Springer Berlin / Heidelberg. Volume Volume 943/1995. Whitten, Jeffrey L.; Lonnie D. Bentley, Kevin C. Dittman. (2004). Systems Analysis and Design Methods. 6th edition. Len Silverston, W.H.Inmon, Kent Graziano (2007). The Data Model Resource Book. Wiley, 1997. Read More