Individual Project 3

CS251: Fundamentals of Database Systems 7

CS251: Fundamentals of Database Systems

Library Database Management System Project

Shannon Jennings

1/8/2016

Table of Contents Project outline 3 Library Management Database Description 3 Description of the Database Design Life Cycle (Week 1 IP)......................................................3 Description of the DBDSL-7 Steps 3 Discussion about the Three Steps in Database Design…………………………………...7 The Entity Relationship Diagram (Week 2 IP)…………………………………………….7 An Entity Relationship Diagram 8 The Logical Model and Normalization (Week 3 IP)……………………………………….TBD Logical Data Model…………………………………………………………………...TBD Normalized to 3NF……………………………………………………………TBD The Microsoft Access Database (Week 4 IP)………………………………………………TBD Database Created in Microsoft Access……………………………………………….TBD SQL Select Statements……………………………………………………….TBD The Microsoft Access database application (Week 5 IP)…………………………………TBD Completed Microsoft Database application………………………………………….TBD Menus, Forms, and Reports………………………………………………….TBD References……………………………………………………………………………………TBD

Project Outline

I have chosen to do a library management system database, my reasoning behind this is a student can check out books and a library can issue them out. There is a different limit of books for each book title that can be checked out due to the library having a certain amount of the same title on had such as 2, 3, 4, etc. Also the number of days the book is able to be checked out is dependent on the issuing date. I am going to have my check out limit date set to 14 days. Also each book in the library has a different ID #, as well as books with same title and same author (the ID # will be based off the number of copies of that book). Entry of all books on hand will be done, and will include, who issued the book, who checked the book out, the date of issuance of the book, the expected return date, and also to be included will be details of amount of fine(if book not returned on expected due date).

Description of the Database Design Lifecycle (Week 1 IP)

A description of the DBDSL-7 Steps

There are seven steps to designing a database:

1. Defining a Mission Statement and Mission Objectives

2. Analyzing the Current Database

3. Creating the Data Structures

4. Determining and Establishing Table Relationships

5. Determining and Defining Business Rules

6. Determining and Defining Views

7. Reviewing Data Integrity

In the first phase of design, defining the mission statement helps to establish the purpose of the database, and provides you with a distinct focus for your work design. (Hernandez, 2013, p 77). The mission statement is a necessity, because it will define anything that needs to be accomplished and gives you directions in the design process. It also serves as a critical assessment of what information will be needed to accomplish the task. The mission objectives are the statements that provide the general task your users can perform against the data in the database. (Hernandez, 2013, p 78). During the defining of mission objectives, you are working with owners, managers, developers, as well as end users. Each of these groups of people will be giving insight and definition to the objectives of the project.

In the second phase of database design, you will be analyzing the current database. This may be an optional step if no previous database existed or there is not a current database in place.

Many of the current databases will be a legacy database or a paper-based database, depending on your organization. A legacy database is also known as an inherited database. A paper-based database consists of forms, index cards, folders, etc. By analyzing the database, it will give you valuable insight on how the organization is currently using and managing its data. (Hernandez, 2013, p 78)

Included with analyzing the database in phase 2, you will be conducting interviews with management; this process will help find out what the needs of the organization are. The developer can ask questions to gain expectations of the organization like how they want the database used, what they like about their current database system, and what they don’t like about it and want to see implemented. After these interviews are completed, the developer can then define the fields in the database along with the calculations in a separate list, and turn them over to the management for further review and refinement.

The third phase in the design process is creating the data structures. In this phase, tables and fields are defined. Primary keys will also be set along with the specifications of the fields being defined as well. The first things that will be defined are the tables, and then the fields will be added next. It is also a very important task to make sure that there is no duplication of data during this process as well. Once it is confirmed that there is no duplication of data, the primary keys will be defined and set to uniquely identify each record. The last part portion of phase three, is to establish field specifications for each field, this is accomplished by conducting interviews with management and users. After the review with management and users, and the refining is completed, the structure is ready for the fourth phase. (Hernandez, 2013, p 80)

The fourth phase is determining and establishing relationships. In this fourth phase, more interviews are conducted to identify relationships with the data. This step is important to complete, because it will ensure relationship integrity. After the relationships have been identified, the next step in this phase is to establish the connections, by the use of primary keys or tables (Hernandez, 2013, p 81). These relationships can be defined as one-to-one, one-to-many, and many-to-many (Hernandez, 2013, p 13).

The fifth phase of the design process involves determining and defining business rules. Once again, you will be conducting interviews which will help identify any limitations and establish business rules. During this phase, you will also implement and define validation tables. Once the constraints are identified they are then defined as business rules. An example of a business rule is the limitation that a shipping date must occur after the order date. An example of a general rule is that there is a limitation on the number of entertainers that an agency can represent. These rules will change as the days go by, but it is important to have these limitations defined to help with data integrity (Hernandez, 2013, p 82).

In the sixth phase of the design process, views are determined and defined. As in many of the previous phases, more interviews will be conducted, but the interviews in this phase will be held with management and users in order to gain a perspective and expectations on accessing as well as viewing the data and information. Each group of users will be accessing the information in specific ways, so this will be useful in productivity of each of the control groups. After the interviews have been completed and the views have put into place, then you are ready for the final phase of the design process (Hernandez, 2013, p 83).

The seventh phase of the design process is to review the integrity of the data. The first step in this phase is to review each table to make sure that it meets the requirements of a properly designed table and that you also check the fields within each table for proper structure and after you make any refinements necessary, you check for table-level integrity (Hernandez, 2013, p 83-84). The second step in this last phase is to review the field specifications for each field. You make any edits or refinements necessary, and then check for field-level integrity. The third step in this phase is to then review the validity of relationships as well as confirm the type of relationship. The fourth and final step in this phase is to then make sure all of the business rules that were identified confirm and meet the limitations specified (Hernandez, 2013, p 83-84).

Once the seven phases are complete, and the database is created, it will be reviewed and refined over time as the organization continues to grow and change. There will be on-going requirements that will need to be adjusted to meet the original design of the database.

Discussion about the Three Steps in Database Design

There are three steps in the database design which include, conceptual, logical and physical data modeling. The conceptual database design consists of creating a data model that is independent of physical considerations. The conceptual design includes three phases, the first phase is creating the ER model, checking the model for redundancy, and validating the model against user transactions to ensure all scenarios are supported (Kamalanathan, 2009).

The logical database design is the process of creating a model of information, which can be mapped into storage objects supported by the Database Management System. In the logical design phase there are two steps which include generating a table from the ER model you created, and the normalization of the tables that you are creating (Kamalanthan, 2009).

The physical database design is what specifies the entire configuration of the database on the storage media. The physical database design has many tasks such as describing the database relations, file organizations, and indexes design used to achieve efficient access to the data, along with knowing any associated integrity constraints and security measures.

Entity Relationship Diagram

I came up with several different relationships and entities in my library management database system. The first one I am going to cover is the relationships that I am going to have books identified with; I have come up with the number of books I have available, the price of the book, the title of the book, the ID of the book, and the author. Since books are published I also came up with a relationship to show the key parts in the publishing, which are the publisher’s name, address, and then both of those are going to be identified to give a publisher ID. The third relationship I created, is the member who checked the book out and how that member can be found in the system such as, name, address, the member type( on if the member is a student or a civilian), when the member created a library account, and the library card number. The last relationship I am going to have pointed out is the due date of the book, the issue date of the book, and the date the book was returned to incorporate any fines into the member’s account. Comment by Diaz: MISSING all cardinality and semantic verbs (active verbs, two per relation), as explained in the chats and the examples.