Your Project Topic Is Zoo. You May Develop A Database For Any Department(s) Or Service(s) Related To A Zoo. When Working On The Project, Make Sure To Follow All The Guidelines Given In The Project Description: Part 1 Section Posted In The Project Description Folder On Blackboard.This class project gives you an opportunity to apply the ideas and concepts of relational databases that you have learned in the course. You will be required to design and implement a relational database for a given scenario. Project topics may be found on Blackboard in the Project Description section under the Project Topics link. To simulate the implementation of a real-life database, you will start with requirements elicitation for a given scenario. After all the basic functional and non-functional requirements have been gathered, you will start designing the database. In this process, you will need to first design the database schema, showing in detail all the relations, data types, relationships, and constraints to be used in the database. You must draw schema diagrams using Microsoft Visio. After the design phase is over (Part 1), you will actually implement the schema in MySQL by creating the required tables with appropriate data types together with relationships and constraints. Next, you will populate all the relations in the database with valid data. The final phase of the project is the retrieval of data from the database. This will be Part 2 of the project. Part I: Part I is 60% of the total grade of the project: 5% for the draft 55% for the final result The draft must include at least the description of your company, business rules, and an ERD. Print out your draft and submit it at the beginning of class on the day when Test 1 is scheduled. Discuss the draft of the project with your instructor at the end of class. If the draft is approved by the instructor, you need to complete the project, get it into Microsoft Word format, and upload the file through the SafeAssign: Project, Part I Submission link on Blackboard before the project is due. Part I consists the following sections: Title Page that includes: Title of the project Course name Course number Name of the student Description that includes: Screen shot from Blackboard confirming your topic Description of the company (1 paragraph) Business requirements for the database (1 paragraph) Business requirements will explain why your company needs a database, what kind of data you are planning to store in the database, and how your company is going to utilize this data. Business Rules: Each business rule must follow the format used in class Each business rule must describe a relationship in both directions Each entity and each relationship used in the database must be described in this section Each entity and each relationship described in this section must be presented on the ERD Entities that includes: Entity’s name Entity’s description List of attributes Data type of each attribute (numeric, text, or date) Sample data for each attribute Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NULL where applicable) Each entity must be described in the section. You must have a minimum of 4 entities. The total number of entities recommended is 4-5. Relationships that includes: A copy of a specific business rule from the Business Rules section for each relationship described here. Relationship’s type: 1:1, 1:M, M:N. Use business rules to justify your decision. Relationship strength: strong or weak. Use business rules to justify your decision. Optionality for both sides of each relationship: mandatory or optional. Use business rules to justify your decision. Each relationship must be described in the section. Each M:N relationship must be resolved and described. Crow’s Foot Entity Relationship Diagram: ERD must be consistent with business rules and descriptions ERD must show all entities and their attributes, relationships, connectivities, constraints, optionality and cardinalities Tables and attributes names must follow naming conventions used in class Each relationship must be properly labeled If a composite primary or foreign key is used on ERD, explain your reasoning.IT 214: Database Fundamentals
Class Project Description: Part I
This class project gives you an opportunity to apply the ideas and concepts of relational
databases that you have learned in the course. You will be required to design and implement a
relational database for a given scenario. Project topics may be found on Blackboard in the
Project Description section under the Project Topics link.
To simulate the implementation of a real-life database, you will start with requirements
elicitation for a given scenario. After all the basic functional and non-functional requirements
have been gathered, you will start designing the database. In this process, you will need to first
design the database schema, showing in detail all the relations, data types, relationships, and
constraints to be used in the database. You must draw schema diagrams using Microsoft Visio.
After the design phase is over (Part 1), you will actually implement the schema in MySQL by
creating the required tables with appropriate data types together with relationships and
constraints. Next, you will populate all the relations in the database with valid data. The final
phase of the project is the retrieval of data from the database. This will be Part 2 of the project.
Part I:
Part I is 60% of the total grade of the project:
▪ 5% for the draft
▪ 55% for the final result
The draft must include at least the description of your company, business rules, and an ERD.
Print out your draft and submit it at the beginning of class on the day when Test 1 is
scheduled. Discuss the draft of the project with your instructor at the end of class. If the draft is
approved by the instructor, you need to complete the project, get it into Microsoft Word format,
and upload the file through the SafeAssign: Project, Part I Submission link on Blackboard before
the project is due.
Part I consists the following sections:
1. Title Page that includes:
• Title of the project
• Course name
• Course number
• Name of the student
2. Description that includes:
• Screen shot from Blackboard confirming your topic
• Description of the company (1 paragraph)
• Business requirements for the database (1 paragraph)
Business requirements will explain why your company needs a database, what kind of
data you are planning to store in the database, and how your company is going to utilize
this data.
3. Business Rules:
• Each business rule must follow the format used in class
• Each business rule must describe a relationship in both directions
• Each entity and each relationship used in the database must be described in this
section
• Each entity and each relationship described in this section must be presented on
the ERD
4. Entities that includes:
• Entity’s name
• Entity’s description
• List of attributes
• Data type of each attribute (numeric, text, or date)
• Sample data for each attribute
• Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NULL where
applicable)
Each entity must be described in the section. You must have a minimum of 4 entities. The
total number of entities recommended is 4-5.
5. Relationships that includes:
• A copy of a specific business rule from the Business Rules section for each
relationship described here.
• Relationship’s type: 1:1, 1:M, M:N. Use business rules to justify your decision.
• Relationship strength: strong or weak. Use business rules to justify your decision.
• Optionality for both sides of each relationship: mandatory or optional. Use
business rules to justify your decision.
Each relationship must be described in the section. Each M:N relationship must be
resolved and described.
6. Crow’s Foot Entity Relationship Diagram:
• ERD must be consistent with business rules and descriptions
• ERD must show all entities and their attributes, relationships, connectivities,
constraints, optionality and cardinalities
• Tables and attributes names must follow naming conventions used in class
• Each relationship must be properly labeled
If a composite primary or foreign key is used on ERD, explain your reasoning.
ABC Bookstore Database
IT 214-001
Student Name
Description of the Project and its Scope:
“ABC Bookstore” is a company that sells books and other related products on-line and in
bookstores located all over the country. The company would like to have a database to
keep track of its inventory. Bookstores today carry more than just books, and “ABC
Bookstore” would like to have each type of product to be represented in the database.
This will allow the company to generate reports summarizing the inventory.
The scope of the project consists of the following sections:
Product Type
Product
Author
The Product Type section describes different types of products sold by the company. For
each product type, the product type code and the product type description must be
recorded.
The Product section describes products sold by the company. For each product, the
product type, product name, price, and number of items on hand must be recorded. If a
product is a book or a CD, some additional information must be included. Particularly,
the company wants to include the ISBN and the publishing date for books, and capacity
for CDs.
The Author section describes authors represented at the bookstores. For each author, the
last name and first name must be included. In addition, the company would like to have
contact information for each author.
Business Rules
1. Each product belongs to one and only one product type. Each type may include
many products.
2. All products share common attributes (the product type, product name, price, and
number of items on hand) but some products have unique attributes (for instance,
ISBN and publishing date for books and capacity for CDs).
3. Each book could be written by many authors but each book must have at least one
author’s name associated with it. Each author could write many books but every
writer must write at least one book.
Entities and Attributes:
Entity #1: PROD_TYPE
The PROD_TYPE entity will contain information that describes different types of
products.
Attribute #1 (PK): PROD_TYPE_CODE
Description: PROD_TYPE_CODE is a unique identifier that will be used as a
reference in other tables.
Data Type: Text
Sample Values: ‘BK’ for books, ’CD ’ for CDs, ’TY’ for toys, etc.
Attribute #2: PROD_TYPE_DESC
Description: PROD_TYPE_DESC will be the name of the product type.
Data Type: Text
Sample Values: ‘Book’, ‘CD’, ‘Toy’, etc.
Entity #2: PRODUCT
Since not all products share common attributes, there is a need for specialization
hierarchy. The PRODUCT entity will include attributes common to all the products. In
addition, two sub-entities must be created to include unique information about books and
CDs (see Entity #3 and Entity #4).
Attribute #1 (PK): PROD_ID
Description: PROD_ID is a unique identifier that will be used as a reference in
other tables.
Data Type: Numeric
Sample Values: 100, 200, 300, etc.
Attribute #2: PROD_NAME
Description: PROD_NAME will be the name of the product.
Data Type: Text.
Sample Values: ‘Harry Potter and the Chamber of Secrets’, ‘Stuffed animal’, ‘etc.
Attribute #3: PROD_PRICE
Description: PROD_PRICE will represent the price of the product.
Data Type: Numeric
Sample Values: 4.99, 15.99, etc.
Attribute #4: PROD_NOH
Description: PROD_NOH will represent the number of items on hand.
Data Type: Numeric
Sample Values: 10, 220, 5, etc.
Attribute #5 (FK): PROD_TYPE_CODE
Description: PROD_TYPE_CODE is a foreign key column that references the
PRODUCT_TYPE table.
Data Type: Text
Sample Values: ‘BK’ for books, ’CD ’ for CDs, ’TY’ for toys, etc.
Entity #3: BOOK
The BOOK entity is a sub-entity and will include unique attributes that describe books.
Attribute #1 (PK, FK): PROD_ID
Description: PROD_ID is a unique identifier that will be used as a reference in
other tables. It is also a foreign key column that references the PRODUCT table.
Data Type: Numeric
Sample Values: 100, 200, 300, etc.
Attribute #2: BOOK_ISBN
Description: BOOK_ISBN will contain the ISBN of the book.
Data Type: Text
Sample Values: ‘978-0439064866‘, ‘978-0590353427‘, etc.
Attribute #3: BOOK_PUB_DATE
Description: BOOK_PUB_DATE will contain the publishing date for the book.
Data Type: DATE
Sample Values: ‘2012-10-04‘, ‘2010-12-23‘, etc.
Entity #4: CD
The CD entity is a sub-entity and will include unique attributes that describe CDs.
Attribute #1 (PK, FK): PROD_ID
Description: PROD_ID is a unique identifier that will be used as a reference in
other tables. It is also a foreign key column that references the PRODUCT table.
Data Type: Numeric
Sample Values: 100, 200, 300, etc.
Attribute #2: CD_CAPACITY
Description: CD_CAPACITY will contain the capacity of the CD.
Data Type: Numeric
Sample Values: 650, etc.
Entity #5: AUTHOR
The AUTHOR entity will contain information that describes authors.
Attribute #1 (PK): AUTHOR_ID
Description: AUTHOR_ID is a unique identifier that will be used as a reference in
other tables.
Data Type: Numeric
Sample Values: 1000, 1001, 1002, etc.
Attribute #2: AUTHOR_LAST
Description: AUTHOR_LAST will contain the last name of the author.
Data Type: Text
Sample Values: ‘Smith‘, ‘Rowling ‘, etc.
Attribute #3: AUTHOR_FIRST
Description: AUTHOR_FIRST will contain the first name of the author.
Data Type: Text
Sample Values: ‘Elizabeth‘, ‘John‘, etc.
Attribute #3: AUTHOR_CONTACT
Description: AUTHOR_CONTACT will contain the contact information of the
author.
Data Type: Text
Sample Values: ‘johnsmith@yahoo.com‘, etc.
Entity #6: BOOK_AUTHOR
Since there is a M:N relationships between BOOK and AUTHOR, there is a need for a
bridge. The BOOK_AUTHOR entity is a bridge that links books in the BOOK table with
corresponding authors in the AUTHOR table. It has a composite primary key.
Attribute #1 (PK, FK): PROD_ID
Description: PROD_ID is a unique identifier that will be used as a reference in
other tables. It is also a foreign key column that references the BOOK table.
Data Type: Numeric
Sample Values: 100, 200, 300, etc.
Attribute #2 (PK, FK): AUTHOR_ID
Description: AUTHOR_ID is a unique identifier that will be used as a reference in
other tables. It is also a foreign key column that references the AUTHOR table.
Data Type: Numeric
Sample Values: 1000, 1001, 1002, etc.
Entities and Relationships:
Relationship: PRODUCT_TYPE “includes” PRODUCT
Relationship type: 1:M
Explanation: business rules state, “Each product belongs to one and only one
product type. Each type may include zero, one or many products”. The
PRODUCT_TYPE entity is on the “one” side of the relationship and the
PRODUCT entity is on the “many” side of the relationship.
Relationship participation: mandatory on the “one” side of the relationship and
optional on the “many” side of the relationship.
Explanation: The PRODUCT_TYPE entity is mandatory in the relationship
because “Each product belongs to one and only one product type”. The
PRODUCT enity is optional in the relationship because “Each product type may
include zero, one or many products”, so a product type may have no products.
Cardinality:
the cardinality of PRODUCT_TYPE is (1, 1)
the cardinality of PRODUCT is (0, N)
Relationship strength: Weak
Explanation: The relationship is weak because the primary key of the PRODUCT
entity does not contain the primary key of the PRODUCT_TYPE entity.
Relationship: PRODUCT “is a” BOOK
Relationship type: 1:1
Explanation: Sine the PRODUCT entity is a super-entity and the BOOK entity is a
sub-entity, there is a 1:1 relationship between them.
Relationship participation: NA
Relationship strength: Strong
Explanation: The relationship is strong because the primary key of the BOOK
entity contains the primary key of the PRODUCT entity.
Relationship: PRODUCT “is a” CD
Relationship type: 1:1
Explanation: Sine the PRODUCT entity is a super-entity and the CD entity is a
sub-entity, there is a 1:1 relationship between them.
Relationship participation: NA
Relationship strength: Strong
Explanation: The relationship is strong because the primary key of the CD entity
contains the primary key of the PRODUCT entity.
Relationship: BOOK “is written” BOOK_AUTHOR
Relationship type: 1:M
Explanation: business rules state, “Each book could be written by many authors
…” and “Each author could write many books …” There is a M:N relationship
between BOOK and AUTHOR so the bridge is needed. When a bridge is created,
it’s linked with two 1:M relationships with the original entities. The BOOK entity
is on the “one” side of the relationship and the BOOK_AUTHOR entity is on the
“many” side of the relationship.
Relationship participation: mandatory on the “one” side of the relationship and
mandatory on the “many” side of the relationship.
Explanation: The BOOK entity is mandatory in the relationship because “Each
book must have at least one author’s name associated with it”. Since every record
in a bridge table (each pair book-author) must reference a record in the BOOK
table, the relationship is mandatory on the “one” side.
Cardinality:
the cardinality of BOOK is (1, 1)
the cardinality of BOOK_AUTHOR is (1, N)
Relationship strength: Strong
Explanation: The relationship is strong because the primary key of the
BOOK_AUTHOR entity contains the primary key of the BOOK entity.
Relationship: AUTHOR “writes” BOOK_AUTHOR
Relationship type: 1:M
Explanation: business rules state, “Each book could be written by many authors
…” and “Each author could write many books …” There is a M:N relationship
between BOOK and AUTHOR so the bridge is needed. When a bridge is created,
it’s linked with two 1:M relationships with the original entities. The AUTHOR
entity is on the “one” side of the relationship and the BOOK_AUTHOR entity is
on the “many” side of the relationship.
Relationship participation: mandatory on the “one” side of the relationship and
mandatory on the “many” side of the relationship.
Explanation: The AUTHOR entity is mandatory in the relationship because “Every
writer must write at least one book”. Since every record in a bridge table (each
pair book-author) must reference a record in the AUTHOR table, the relationship
is mandatory on the “one” side.
Cardinality:
the cardinality of AUTHOR is (1, 1)
the cardinality of BOOK_AUTHOR is (1, N)
Relationship strength: Strong
Explanation: The relationship is strong because the primary key of the
BOOK_AUTHOR entity contains the primary key of the AUTHOR entity.
Entity Relationship Diagram (ERD):
PRODUCT
(0,N)
PK
PROD_ID
FK2
PROD_NAME
PROD_PRICE
PROD_NOH
PROD_TYPE_CODE
d
includes
PROD_TYPE_CODE
‘CD’
BOOK
CD
PROD_ID
PK,FK1
BOOK_ISBN
BOOK_PUB_DATE
PROD_ID
CD_CAPACITY
(1,1)
written
(1,N)
AUTHOR
BOOK_AUTHOR
PK,FK1
PK,FK2
PROD_ID
AUTHOR_ID
(1,N)
PK
PROD_TYPE_CODE
PROD_TYPE_DESC
‘BK’
PK,FK1
PRODUCT_TYPE
(1,1)
(1,1) PK AUTHOR_ID
writes
AUTHOR_LAST
AUTHOR_FIRST
AUTHOR_CONTACT
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.