Database design and implementation

Assumption

In order to draw the conceptual data model diagram (i.e an Entity Relationship Diagram using the Chen notation), I made some assumption on the scenario which explains about the Database Library System for University of Greenwich (UoG).

As the scenario said that students from other campuses are entitled to use the library or borrow from it and there were also 1,556 staff members who are entitled to borrow items from the library, I assumed that there may have two main types of members in the UoG library system. So I split staff and student as two main types of members in the Database Library System for University of Greenwich (UoG). So I took out them as two entities included in my EERD and as they are a part of member, I split up these two entities with member entity by using disjoint mandatory sing. Moreover, according to the scenario, I assumed that there may have two sub-types of students so that I separated the undergraduate entity and postgraduate entity up with student entity. As these two sub-entities may not be established as tables in the database, I used overlapping mandatory sign in my EERD.

Moreover, in the scenario, it said "For simplicity and for the purpose of the coursework, we are only interested in books, journals and video items." Thus, I assumed that there are three main types of items in the library system and these three entities have one-to-one relationship with item entity so that I divided these types from the item entity with disjoint mandatory sign in my EERD. Furthermore, I assumed that books may be categorized into three types according to the coursework specification. It said that the librarians had a list of some books they are interested in acquiring but cannot obtain, such as rare or out-of-print books and books that were lost or destroyed but have not been replaced. Thus, I distinguished the books into three types; for demand, for loan and for reference in my EERD. As I assumed these may be not primarily important for the library database system, I only put them as optional in the EERD by using overlapping mandatory sign in order to link these three sub-entities with book entity. Additionally, according to the scenario, I assumed that it may be able to classify the for reference entity into reference books, rare books and maps. Therefore, I drew that part of EERD with overlapping mandatory sign.

According to the scenario, I assumed that members may be able to borrow and reserve copy items and a member can borrow and reserve more than one book and one book can be borrowed or reserved more than one time. Thus, I drew many-to-many relationships between them. Moreover, I assumed that borrow and reservation entities are dependent on others as well as their relationships, I drew these entities and their relationships as weak entities and weak relationship. Furthermore, in the scenario, it said that books could be checked out form one week on short loan or one academic semester for ordinary loan. Therefore, I assumed that there may be two main loan types; short loan and ordinary loan and the due date of borrow may be different according to the loan type. Thus, I drew one-to-many relationship between loan type and borrow and as short loan and ordinary loan are not chiefly required in the database system so that I drew the overlapping mandatory relationship between short loan, ordinary loan and loan type. In addition, as the scenario said that there was a fine calculation on daily basis on overdue books, I assumed that fine may be calculated based on the return date in the return table. Thus I drew the one-to-many link between return entity and fine entity.

Normalization

Librarian-------------(LibararianID, LibrarianName, Gender, DateofBirth, Post)

Loan Type-------------(LoanTypeID, LoanType, Description)

Member----------------(MemberID, MemberName, DoB, Gender, Address, Phone, Fax, Email)

Student---------------(MemberID, StudentID, CurrentAcademicYear, CourseID, CampusID, StudentType)

Course----------------(CourseID, Course, Description)

Campus----------------(CampusID, CampusName, Description)

Staff-----------------(MemberID, PostID, DepartmentID)

Post------------------(PostID, PostName, Description)

Department------------(DepartmentID, DepartmentName, Description)

Borrow----------------(BorrowID, BorrowDate, LoanTypeID, LibrarianID, MemberID)

Borrow Detail---------(BorrowID, CopyItemID)

Return----------------(ReturnID, ReturnDate, BorrowID, MemberID)

Return Detail---------(ReturnID, CopyItemID)

Fine------------------(FineID, FineDate, ReturnID, MemberID)

Fine Detail-----------(FineID, FineTypeID, CopyItemID)

Copy Item-------------(CopyItemID, ItemID, Status, SupplierID)

Supplier--------------(SupplierID, SupplierName, CompanyName)

Fine Type-------------(FineTypeID, FineType, Fine, Description)

Item------------------(ItemID, Title, SupplierID, PublisherID, YearofPublication, Language, Keyword, Price)

Book------------------(ItemID, ISBN, Binding, AuthorID, Series, EditionNo, Category)

Journal---------------(ItemID, VolumeNo, DateofPublication, EditorName)

Video-----------------(ItemID, GenusID, Duration, AuthorID)

Genus-----------------(GenusID, Genus, Description)

Author----------------(AuthorID, AuthorName)

Publisher-------------(PublisherID, PublisherName)

SQL code

A1.

SELECT Borrow.BorrowID, Member.MemberName, LoanType.LoanType, LoanType.LoanTypeID, Item.Title, Item.YearofPublication, Item.Language, Supplier.SupplierName

FROM Borrow, Member, LoanType, BorrowDetail, Item, Supplier, CopyItem

WHERE Member.MemberID=[Enter Member ID] And Borrow.BorrowID=BorrowDetail.BorrowID And Borrow.MemberID=Member.MemberID And Borrow.LoanTypeID=LoanType.LoanTypeID And BorrowDetail.CopyItemID=CopyItem.CopyItemID And CopyItem.ItemID=Item.ItemID And Item.SupplierID=Supplier.SupplierID;

A2.

SELECT Member.MemberID, Student.CurrentAcademicYear, Count(BorrowDetail.BorrowID) AS NumberofBorrow

FROM Member, Borrow, BorrowDetail, Student

WHERE Borrow.MemberID=[Enter Member ID] And Student.CurrentAcademicYear=[Enter Current Academic Year] And Borrow.BorrowID=BorrowDetail.BorrowID And Borrow.MemberID=Member.MemberID And Member.MemberID=Student.MemberID

GROUP BY Member.MemberID, Student.CurrentAcademicYear;

A3.

SELECT Borrow.LoanTypeID, BorrowDetail.CopyItemID, LoanType.LoanType, Count(Borrow.LoanTypeID) AS Total_Loan

FROM Borrow, LoanType, BorrowDetail, CopyItem

WHERE LoanType.LoanType=[Enter Loan Type] And Borrow.BorrowID=BorrowDetail.BorrowID And Borrow.LoanTypeID=LoanType.LoanTypeID And BorrowDetail.CopyItemID=CopyItem.CopyItemID

GROUP BY Borrow.LoanTypeID, BorrowDetail.CopyItemID, LoanType.LoanType;

A4.

SELECT Item.Title, Book.ISBN, Item.YearofPublication, Book.Binding, Item.Language, Count(Item.Title) AS NumberofTitle

FROM Item, Book

WHERE Item.Title=[Enter Title] And Book.ItemID=Item.ItemID

GROUP BY Item.Title, Book.ISBN, Item.YearofPublication, Book.Binding, Item.Language

HAVING Count(Item.Title)>0

ORDER BY Item.Title;

Screen Design

Screen dumps for all required application in D6

References

www.cs.sjsu.edu/faculty/.../25Sp157AL5Enhanced%20ER-diagram.ppt

http://www.databasedev.co.uk/open-query-macro.html

http://www.ehow.com/how_13637_create-report-microsoft.html

Please be aware that the free essay that you were just reading was not written by us. This essay, and all of the others available to view on the website, were provided to us by students in exchange for services that we offer. This relationship helps our students to get an even better deal while also contributing to the biggest free essay resource in the UK!