SQL New Library Project

Page 1 of 3
CIS 255 Project: instructions and rubric
Overview:
 You’re going to be tasked with designing a relational database for a “new library” in town
 It will include the following:
o A proposal,
o Business rules,
o Attribute names and definitions,
o An ERD (Entity-Relationship Diagram),
o Should be implemented in Oracle/SQL
 Comments, comments, comments!
 If I leave the company and YOU get hired to take my place, wouldn’t
your life be made easier if my code/work is fully documented?
 Tables as required
 Relationships between those tables
 Constraints on data and proper data types
 Saved queries which I can run against the data you’ll populate your DB with
o Cardinalities
o Any required primary and foreign keys
o If required, super and sub-types
o Bother inner and outer joins
Specific Instructions:
 Part 1: The story of the business
o Why would a library WANT an electronic database system?
o One-to-two sentences about the mission of the library
o One-to-two paragraphs to describe the high-level processes you will employ
o One-to-two paragraphs to describe (in more detail) the processes, data, and business
rules
o In short, think about the NON-TECHNICAL “stuff” that goes on at a library and write this
in such a way that someone who has never heard of a library can read this and
understand
 Part 2: The business rules:
o If you were the librarian or a patron of the library, what rules would you have to follow?
o Rules for borrowing/returning books?
 What if someone returns their items late?
 Can a patron borrow additional items if I owe a “late fee”?
o How does a patron get a library card?
o How are new books, periodicals, etc. added to the library’s inventory?
o How are old/damaged items removed from the library’s inventory?
o Can ANYONE get a card at your library, or must they live in an approved geographical
area?
 Part 3: The attribute names and definitions
o The library has books, DVD’s magazines, etc., correct?
 How are those items categorized?
 What information must the database have to “keep track of” those items?
Page 2 of 3
 What information must the database have so patrons can search for items?
o Does your library have multiple locations?
 Part 4: A detailed Entity-Relationship Diagram (ERD)
o This must reflect all the business rules (part 2) AND the entities (part 3)
o Should follow “best practices” for ERD design
o Should show proper relationships, keys, and cardinalities
 Part 5: the SQL/Oracle statements:
o To create tables as required
o The INSERT statements to get a “reasonable amount” of data (at least 100 items across
all entities)
o The proper keys defined
 Part 6: the queries that you will run to get data returned to you
o Make proper use of clauses such as
 WHERE,
 IN,
 GROUP BY,
 HAVING
o Two functions
o One calculation
o An INNER JOIN on at least three tables
o An OUTER JOIN
o Your queries should be properly documented in a way that someone who is covering for
you can read them and understand the purpose
Grading rubric:
 This project will be worth 30% of your overall grade in this course.
 The project is divided into SIX “categories”:
o The story of the business: 5% of the overall grade
 Well-written
 Consistent with, and complementary to the deliverable
o The business rules: 5% of the overall grade
 Well-written
 Consistent with, and complementary to the deliverable
o Attribute names and definitions: 20% of the overall grade
 Easy-to-read
 Consistent with notations in the slides, lectures, and Internet searches
 Comprehensive
o The ERD: 20% of the overall grade
 Easy-to-read
 Contains all entities and relationships
o Data and table creation: 25% of the overall grade
 Easy-to-read
 Contains all entities and relationships
 Has appropriate constraints
 Reasonable amount of data
 Referential integrity enforced
o Queries: 25% of the overall grade
Page 3 of 3
 They MUST WORK!
 Should be commented in “simple language” so that your replacement can read
them and understand