Determining the Database Purpose
The database can be considered as the set of data associated to a specific purpose or subject. The database is either installed in a computer or in a cloud. The relational database system is the most popular database type. The relational database is based on implementing relation among the entities stored in the database. Prior to design the database, the designer must design the whole database. Without a good design it is not possible to implement a fully functioning and scalable database. The database design include the entity relationship diagram, relationships, attributes, constraints and many other things.
The fundamental steps for designing a complete database are as following.
- Determining the database implementation purpose
- Identifying the entities required to support the purpose
- To recognize the attributes that can be the basic structure of the entities
- To understand the constraints that will be applied to the attributes
- Recognizing the relationships among the tables
- Making the database entities in 3rd Normal form
- Adding data to the entities so that it can be meaningful
Determining the Database Purpose: The database is prepared for storing the data and activities done in the library. The database will be storing the details of the members, books and rents mainly. However, the other process associated with the library will need to be recorded. Taken as an example, a member can search books of a specific writer. Therefore, storing the information of each author and which books they have written is important. The library also impose fines when the books are not returned on time. Therefore the database will also store the fine related information. It is essential to complete the planning part before start implementing the database in Oracle. It is because, planning can seem to be time consuming but if not planned properly a complex database can take ten times more time to be completed without planning.
Determining the Tables: This is considered to be one of the trickiest parts in the whole database design process. This part is extremely tricky as the outcome of the database does not provide any assumptions regarding the structure of it. The structure has to be done from scratch without having no idea of what can be the outcome, up to a certain point. This process needs to be done by an extremely skilled and experienced person. It is essential that database structure can prevent storing same data twice. However, for meeting the purpose few data will be used various time but this repetitive data must be restricted only one in the whole table. All the columns of the table must be related to the purpose of the database. Taken as an example, if the database just wants to store member data then it is not required to store parent data. However, in case of hospital system it is essential to have a relative data so that in case emergency he/she can be contacted. Each data in the table must in proper data type. Just because Varchar2 can store almost all kind of data, a designer can use only Varchar2. Taken as an example, in case of money decimal or similar data type must be used.
Determining the Tables
The indexing has been introduced into the database for optimizing the performance of the database. This achieved through reducing the number of disks required to access for executing a query. The index can be considered as a type of data structure which can be used for locating and accessing the data within a database very efficiently. The indexing methods are four types such as ordered indices, primary indices, clustering indices and secondary indices. The primary indices can be further be categorized into dense and sparse indices. If the name column of the member is indexed, then the database can easily search the name as the table will reduced to the name column only. This way the efficient identification of data is achieved through the indexing method.
Determining the Fields: The attributes serves as the basic static structure of the entities. The attributes represent different characteristics of real world representation of attributes. The attributes should have single value. The reason will be demonstrated in the normalization step.
Constraints: Constraints can be of many types like unique, primary, referential, check, not null and many more. The declaration of constraints are extremely important for defining the dynamic nature of the database entities. Each of the mentioned constraints define a specific characteristic of the attribute. It is not necessary that every attribute must have a constraint but to make an entity fully operational and correct it is essential to add constraints at required attributes.
Relationships: The relationships determines how the database would respond to user request. The relationships can be divided into two parts, such as cardinality and optionality. The cardinality means one or many row of an entity can be related to one or many rows in another table. Taken as an example, one member can rent many books. The optionality means a row may be related to row to another table. Zero means can or cannot be related and One means that row have to be associated to at least one row of another table. Taken as an example, a book may not borrowed by any member. In this case, the book will have zero optionality with the rent entity. However, a rented book has to be from the book entity in this case rent will have One optionality with book.
Normalization: Normalization is the process of preventing any data anomalies from the database. The normalization has up to 5 normal form. However, for most of the databases, 3rd normal form is taken into consideration. The firs normal form states that the tables must have atomic values (no multivalued attributes are accepted) and each entity must have a primary key. The second normal form describes how the entity prevents partial dependency among its attributes. The partial dependency occurs when non-key attributes are dependent on another non-key attributes. The final stage is third normal form. In this normalization stage, transitive dependency is eliminated. The normalization process divides the set of raw data into various tables. The relationship among the tables define the purpose of the raw data and if properly traced then every raw data can be accessed from the tables.
Indexes for optimizing performance
Adding Data: Data are real meaningful element of the database. The data are entered through GUI or command lines. Most of the RDBMSs use similar kind of insert queries to fill the tables with data. Based on the constraints, data types and other few predefined elements, the database management system validates the entered data.
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
The business rules of the proposed database are as following.
- One member can buy many books. The requested book has to be stored in the database. A member have to be registered to rent books.
- One book can be rented many times. It is not mandatory that every available book has to be rented at least once.
- Each book will have only one publisher
- One book can have many authors and one author can write many books
- One rent may have a fine if the expected return date is overdue
First Normal Form: According to first normal form, every column that has multivalued attribute has to be made atomic valued attribute. The main primary key is considered to be memberID. Now the whole database perception is based on member view. If every column is made atomic then member, book, author and publisher details will be repeated every time a rent is made.
Second Normal Form: Now to make the database more efficient, partial dependencies are identified. As shown in the figure 2, the partial dependencies are associated with each unique values. However, only book, rent and author details are stored in a different tables as they make many-to-one or one-to-many relation with either book or member.
Third Normal Form: Now the transitive dependency comes into the big picture. The publisher and fine details are stored into another table so that transitive dependency can be removed from the tables. After removing these dependencies as shown in the figure 2, the database will look like the ERD showed in figure 1.
Figure 2: Dependency Diagram
(Source: Created by Author)
Queries:
- All the details of the members
- Book name and its authors
- Number of books published by a publisher
- How many times each member has made fines
- Books and its publisher details
Reports:
- The first report is active rents
- The total amount of fine per month
- Details of every book that has not been returned ever
- Details of each member
- Details of most five frequent members
Forms:
- Forms for member registration
- Forms for new book detail insertion
- Forms for new author identification
- Form for new publisher details
- Form for renting books
- Form for paying fine
|
Table |
Code |
|
Member |
CREATE TABLE Member memberID NUMBER (5) PRIMARY KEY, name VARCHAR2 (40), address VARCHAR2 (200) |
|
Publisher |
CREATE TABLE Publisher publisherID NUMBER (5) PRIMARY KEY, name VARCHAR2 (40), address VARCHAR2 (200) |
|
Author |
CREATE TABLE Author authorID NUMBER (5) PRIMARY KEY, name VARCHAR2 (40), address VARCHAR2 (200) |
|
Book |
CREATE TABLE Book bookID NUMBER (5) PRIMARY KEY, name VARCHAR2 (40), publisher NUMBER (5) Constraint publisher_fk REFERENCES Publisher(publisherID) |
|
BookAuthor |
CREATE TABLE BookAuthor bookID NUMBER (5) Constraint book_fk REFERENCES Book(bookID), authorID NUMBER (5) Constraint author_fk REFERENCES Author(authorID), authorNumber VARCHAR2 (10), Constraint book_author_pk Primary Key(bookID, authorID) |
|
Rent |
CREATE TABLE Rent rentID NUMBER (5) PRIMARY KEY, bookID NUMBER (5) Constraint book_rent_fk REFERENCES Book(bookID), memberID NUMBER (5) Constraint member_fk REFERENCES Member(memberID), rentDate Date, expRtnDate Date, actualRtnDate Date |
|
Fine |
CREATE TABLE Fine rentID NUMBER (5) Constraint rent_fk REFERENCES Rent(rentID), amount Number(8,2), Constraint rent_fine_pk Primary Key(rentID) |
|
Table |
Code |
|
Member |
Insert Into Member Values (1, ‘Ellie Harford’, ’32 High Street’); Insert Into Member Values (2, ‘Aaron Vaughan’, ’12 Shadforth Street’); Insert Into Member Values (3, ‘Jai Jenkins’, ’24 Raglan Street’); |
|
Publisher |
Insert Into Publisher Values (1, ‘Allen & Unwin Book Publishers’, ’83 Alexander St Crows Nest, NSW 2065 AUSTRALIA’); Insert Into Publisher Values (2, ‘Text Publishingn’, ‘Swann House 22 William Street Melbourne VIC 3000 AUSTRALIA’); Insert Into Publisher Values (3, ‘Margaret River Press’, ‘PO Box 47 Witchcliffe WA 6286’); |
|
Author |
Insert Into Author Values (1, ‘Bethany Herrod’, ’44 Mt Berryman Road’); Insert Into Author Values (2, ‘Oliver Sawers’, ’11 Ferny Avenue’); Insert Into Author Values (3, ‘Jonathan Simmons’, ’63 Shannon Court’); |
|
Book |
Insert Into Book Values (1, ‘Cloudstreet’, 2); Insert Into Book Values (2, ‘Picnic at Hanging Rock’, 3); Insert Into Book Values (3, ‘Seven Little Australians’, 1); |
|
BookAuthor |
Insert Into BookAuthor Values (1, 1, ‘First’); Insert Into BookAuthor Values (2, 1, ‘First’); Insert Into BookAuthor Values (2, 3, ‘Second’); Insert Into BookAuthor Values (3, 2, ‘First’); |
|
Rent |
Insert Into Rent Values (1, 1, 1, To_Date(’04/02/2019′, ‘DD/MM/YYYY’), To_Date(’11/02/2019′, ‘DD/MM/YYYY’), To_Date(’09/02/2019′, ‘DD/MM/YYYY’)); Insert Into Rent Values (2, 1, 2, To_Date(’28/01/2019′, ‘DD/MM/YYYY’), To_Date(’04/02/2019′, ‘DD/MM/YYYY’), To_Date(’04/02/2019′, ‘DD/MM/YYYY’)); Insert Into Rent Values (3, 2, 3, To_Date(’22/01/2019′, ‘DD/MM/YYYY’), To_Date(’29/01/2019′, ‘DD/MM/YYYY’), To_Date(’31/01/2019′, ‘DD/MM/YYYY’)); |
|
Fine |
insert into fine values (3, 2.0); |
Command 1: Select * from Member;
Command 2: Select b.name, a.name from book b inner join bookauthor ba on b.bookID = ba.bookID inner join author a on ba.authorID = a.authorID;
Command 3: Select Count(b.publisher), p.name from book b inner join publisher p on b.publisher = p.publisherID Group By p.name;
Command 4: Select Count(f.rentID), m.name from member m inner join rent r on m.memberID = r.memberID inner join fine f on r.rentID = f.rentID Group By m.name;
Command 5: Select * from book b inner join publisher p on b.publisher = p.publisherID;
References
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Dandan, M. and Jing, B., 2017, September. Design and Implementation of the Comprehensive Information Platform for Smelting Enterprises. In Computer Network, Electronic and Automation (ICCNEA), 2017 International Conference on (pp. 285-288). IEEE.
Gould, H., 2015. Database design and implementation: A practical introduction using Oracle SQL.
Liu, D., 2017, May. Research on Big Data Volume Storage Structure Design and Quick Query Mechanism of Oracle Database. In 2017 2nd International Conference on Materials Science, Machinery and Energy Engineering (MSMEE 2017). Atlantis Press.
Zheng, S.Y., Deze, Q., Zhu, Q. and Li, B., 2015. Design and Implementation of Supermarket Personnel Management System Based On Java. In Proceedings of the 2015 International Conference on Education, Management and Computing Technology, Tianjin, China (pp. 1724-1727).
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
