Answered You can buy a ready-made answer or pick a professional tutor to order an original one.
Database Class Project
PLEASE HELP ! Need serious help with this my final database Project. Thank you !
Create a database project and indicate a problem (work or personal). Describe the problem you are trying to solve .
1) Using Visio draw the ERD for your project,
2) Discuss whether it is in 3NF and why,
3) Create an SQL Database and insert at least 5 records in each table.
4) Create 5-7 queries to retrieve data that would be useful.
Documentation should include:
a) The project description,
b) The ERD,
c) any business rules,
d) The normalisation discussion,
e) The questions that your queries answer.
- @
- 17 orders completed
- ANSWER
-
Tutor has posted answer for $30.00. See answer's preview
******* Management System
Name:
Institutional
************
Date:
A
********
Design
for *******
********
Abstract
*******
*********
******
**
***
******
********
**
********
the
*********
********* in ******* **** as **************
**
material
****
********** ***
borrowing
*********
*******
******* are ******** ** the ********* to ****** ***** resources ** an ********* ****** *** ***** **** libraries remain *********** *** **** ********** ** ****** *** switch **** ** *** ******** ** *** following sections * **** ******* *** *********** ** ******** manual ****** ******** ** * college ******* *** **** ****** ***** on the ********
**
*******
*
centralised repository ******** *** library administrator to ***** access *** process *** information when required * ********* of *** database to **** the *********** will ** ******** *********** *** available techniques *** ************* *** then ******* *** *** system prototype **** ** ******* ** *** light ** *************** *** outcomes ** *** ******* ******** and ****** ***** **** **** ** *********** *** **** *** ********** *** ********* ** *** ****** * will *** ********** ******** ****** ** ********* *** ******** between assets ** library *** ***** **** ***** *** **** library holds Report **** also ******* * ******** ************ ** the ***** prototype *** ************* of adopting the ************* *** technologies ************* Background Library ******** ****** is ******** ** ******** *** library ****** in * ******** ****** ********* ** ***** * ******** ******** ****** ** * ******* ******* ** *****
*******
of
***
library
***
keep
*****
**
************
**
*****
***********
*****
membership
******
****
***
*******
**
*****
**
college
****
can
****
**
*****
********
The
****
*****
***
**
define
***
******** attributes *** their ************ ** ****** * ******** **** *** ****** manage the operations ** *** ******* ************ *** overall *************** ** *** database ** ******** to ******** *** *********** ** **** ****** redundancy *** ************* to data *********** ***** *** ** ** provide ******** ******** *** ******* ******* ***** **** **** to minimize
***
***********
**
data *** *** **** ***** filling ****** *** ** ***** the processing of **** very easy ******* ***** of time Scope **** ******* ******** that ***
educational
******
can
*** ** ** ***** ********* to ********* ***** *** author ****** publisher’s detail availability ** books keep ***** ******* record ***
**
**
****
****
to
modify ** ****** changes ** ******** ** does not ******* ****** ****** ** library ********** ***** ***** *** in **** server ** Visio ***** MS **** ***** ***** ** ******* ********** ******* There are *** ***** ** *******
management
*********
Old
*********
******
** used ** **** ******* **************** detail ********* *********** Library systems Preferred ******* **** are ********* ******* wich ******* more ******** ******* ** storing information and ******** *** ******* ********* **** *** **** efficient *** ****** ** maintain ** Advantages ** Computer ***** ******* management system ** ********
****
********
of
library **********
*********
***********
**
*** ******** ******* workforce ******
to
maintain
library resources ****
*********
**
******** because ****
****
***
****
** spend ***** ** ****** *****
**
is ******** ********** duplication ** ******** ****** less
*****
*******
*****
** ** ***** **** ****** ** ** ***** based Secure *****
***
***
********* *********** **** **** ***** ********** ******* ** access some **** functionalities ************* Risk ** ******** ** software ******** Data *** be ******** *** corrupted ** ******** malwares ******** skilled
******
***
**********
** ******** **** ********** **** ** ********* file-based ******
****
computer
*****
******* ******* System ******** ****** ** ***
*******
is
****** ****** which *** ****
**
functionalities
** *** ** ***** ****** of books
***
*****
***** ******* system ** *** comfertable ** handle ***** transaction ********* ******* ****** **** *** Membership ******** *****
**
***
membership ** ******* *
**********
**********
*********** available ** ******** and ******* **** **** ** ********* **** ******* ** ********* *****
are
refundable
** *** *** *** ******* ****** books ******** ******* for ***** then ********* issues them books
*****
by
********
** registers
***
*****
*****
*** ****** ** ******* else or
***
it
**** ** long **** ****** found book he took
****
card
**** *** **** *** *****
***
****
** ***** *** ****** ** **** *** ********* ******* membership **** ***** with
this
****
after
that
***
** **
******
******** ***** **** ****** ** **** category **** Student *** hold ******* ** **** **** *** ***** ****** books **** student **** ** return **** the librarian check in a ****** ******** ***
****
** ***** *** ****
**
******
**
****
along
with
the
**** **** *** ******* card and return *** ******* membership card to ******* and
place
**** ** rack ** ****** manner **** *********** ** *** *** **** of ***** ** passed ****
*********
**********
***
**********
*******
***
*****
**** **
****
***
******
date of **** **** ** considered ** ** ** p *** *** after *** date
*******
****** *********** ********* ** arrange all **** ** ************ ****** **** **** ** **** **** ******* More staff
**
******** to ****** ******** ********** By ****** ******* ** ***** *** ***** ****** ** card ***** create problem
to
identify ***** **** ** issued to ***** ******** ************ of ****** **** ** **** ** ****
***
**** ** different ********** **** ** assets Difficult ** ********** *** *** ********* ***
System
***
features
*****
****
be ************
****
********
******
*** library ***** *** ****** *** ******* regarding library *** ******** its assets ************ Features of ******** ******* ****** ** each ************** ** ****** ** ********* ***** number of ****** ** ***** *** ****** maintained **** ** ******** **** **** ******** *** ** viewed List ** *********
*****
***
**
*******
Fine
** ******
**********
by
system
************** ********** can add/modify/delete ******** and ****** Can see *** **** ** issued books List ** ***** *** returned ** time ** ******** their id or name ** book ********* ** **** ****** **** ******* **
database
********* *** ****
in
*******
if
**** ** book is ****** to ******* ****
or
****
********** has ******** *** ***** ******
**
database
so ** is secure ******** Tasks of
**********
are
following:
***
members
*** library cards *** assign them to members ****** ******** ***** ****** Add books/ assets ** *** database ****** assets **** ***
*********
*******
******** from ******* *** update *** fines View all member’s detail *** System
*******
There
*** ***** ******
**
********
****** design ***** *** ** **********
******
******
is
******* ******
and
***** ** physical ***** ** ***** **** *** **** ****** ** ******** ** our ****** all these ***** phases **** **** ******** ***
*********
************ ********** ****** ***
*********
**********
**
******************* model * ********* ************** ** entities and
*****
*************
to **** ***** typically **** ** computing ** ******
**
the
************ of **** ****** ***************** ** ** cleared ****
above
**********
**** **** entities ** ******* *** *******
(student
**
****** ***** *** ********** ***** ***** ****
regarding
any
******* ** *** **** books ***
********
****
******* *** ** **** ******* ** try
to
****
******** ******** *** ***** relationship with each ***** and **** attributes they **** ***** **
******
**********
** **** ***** *** ** reduced and ********** * **** ******** to **** ******** ***** ********* *********** ** the relationship/Multiplicity ** ********* **** **
******
***
***** many ***** (1:*) * book *** **** **** ******* (1:*) * ****
***
****
many
**********
******
*
****** *** be ********** ****
zero
**
****
***** ********
******
*
******** *********** **** **** ** ********** **** ***
***
only
*** ******
******
Books
*** ****
****
**
many transactions…… *** *** ********* ******
****************************
**
**** Librarian ******* ************************** **
****
*********
******* fine ******************* ** :*) ****** transactions on
*************
******
******* pays fine… ************************************************
(1:*)
*******
***
have **** ***
membership………
** **** logically *** *** in ******** its *** ******* ***** **** ** ****** *** active *********** ************ ** when ***** object ** dependent on ****** ******* ***** as ****** ***** - * * * * - * * - Non-identifying relationship is
****
***
primary
key attributes ** the
******
must *** ****** ******* *** ********** of the ****** ******* as continuous **** : ** **** ******* ** can *** that entity ****** *********** ** dependent ** *********
*****
***
*******
******** ******* *** ***** transaction ***** three ************ *** necessary A ********* *** ***** ***** ********* ******** ********** *** **** ************ ** books *** *** **** *** **** **** members Because many transactions can ** **** ** many ***** at same time ** ********* ***** **** transactions In this EERD ** *** *** *** ***** **** ** **** ********* ** ***** *** author *** ********* *** ***** *** ********** ****** **** ******* show ************
**
******** *** reduce ********** problems ** well
Logical
Design *********** ******** ******** After ** ******
**
******* ****** ** ***** new ********* *******
********
and try ** summarise them **** ******
*********
********** ******** ***** their ************* ****** Table: ******* Column ***** ******* ****
****
type *** ***** ******** ********* ***** Yes ******** **** ******* *** ************ *********** No
************
No ***** Name ** Author last_name *** VARCHAR(40) No
****
Name ** ******* *************** *****
************
type Parent ****** ************ ***********************
************
******* **************** Zero Or ***** ******
Books
Column ***** ******* **** Data
type
*** ***** ******** book_id *****
Yes
******** **** ******* *** (book_id) ****** ***** ***
INTEGER
*** ******* *** ******** ********** ****************** book_title No VARCHAR(40) *** ***** of ***** ********
No
************
No
Name of ********** ************* *** ***** *** Book ********* ** not
**************
No ******** *** ******** of ****** ****** *** ************ *** ***************** *************** ***
INTEGER
*** **** ******* *********** *** INTEGER No Rows in * ***** ******* *** ***** *** ** **** ****** ****** or
lost
**** ** ***** ** *********
**
*********** *** ************ *** *** ******** ******** ******* **** ** ********* *** ******* ***** ** *** Updated_by *** VARCHAR(40) No *** ******** ******** ******* **** ** ********* who ******* ***** in *** created *** ********* *** **** time ** creation updated *** ********* No **** **** of updating *************** Name ************ ***** ******* Child ************ books_books_by_author ************ books books_by_author **** ** More books_books_by_category Identifying ****** ****************** **** Or ***** ************************* Non Identifying books ******************* **** Or More **************** *** ************ ********** ****** One Or ***** ****** **************** ****** ***** ******* key **** ***** *** ***** ******** ********** **** INTEGER
****
**
&
******* *** (author_id) ********** ******************
book_id
**** INTEGER Yes PK & FOREIGN KEY ********* ********** books(book_id) ************** ***** ************ ***** ******* ****** ************ *********************** Identifying ******* ****************
Zero
**
*****
**********************
************ ****** **************** Zero ** *****
Table:
books_by_category Column
*****
******* ****
****
type ***
*****
Comment category_id
****
INTEGER ****
PK
*****
FOREIGN
KEY *************
**********
************************
********
****
INTEGER
****
PK
***** ******* ***
*********
REFERENCES
***************
***************
*****
Relationship
*****
*******
******
************ books_books_by_category ************ ******
books_by_category
****
Or
*****
*****************************
Identifying
Categories
******************
Zero
Or More ****** ********************
******
*****
*******
****
****
*****
Not
*****
Comment **********************
Yes
********
****
PRIMARY
KEY
************************
*************
***
********
Yes
*******
KEY
**************
**********
librarian(librarian_id)
******** *** ********
Yes
FOREIGN
***
(book_id)
REFERENCES ***************
**********
*** INTEGER
Yes
******* ***
***********
REFERENCES *******************
******************
*** *********
***
****
***
**** is
*********
******************
***
DATETIME
No
****
***
**** is returned
************
*** VARCHAR(40)
***
Who
********
*****
**
********
returned_by
*** ************ *** *** *********
*********
***
*****
***
***
****
**
book
borrowed
************ No *****
No
**
********
*
*********
created_by
***
VARCHAR(40)
No
For internal ********
purpose
****
**
*********
***
*******
*****
in
***
*********** No ************ ***
***
********
********
*******
name
of
*********
who
*******
***** ** DB
********
***
*********
***
****
time
of
*********
********
***
DATETIME
No
****
time
**
updating
***************
*****
************
***** *******
******
************
*************************
***
************
******
borrow_transaction
****
Or
***** ****************** ******************** ***
************
*******************
*******************
****
Or
****
librarian_borrow_transaction
Non
************
********** ******************* Zero Or ***** *************************** *** ************ members ******************* One **
*****
******
***********
Column
*****
*******
key
Data
*****
*** ***** ******** category_id **** INTEGER Yes ******* KEY ************** ************** No
************
***
****
of
category
**
*******
*****
****
Relationships: ***** ************ ***** Parent Child ************ Categories_books_by_category ************ *********** books_by_category Zero
**
*****
Table:
Fined_Transactions
Column
name
*******
key
Data
***** *** ***** ********
fined_transaction_id
****
********
Yes
*******
***
(fined_transaction_id)
member_id
No
******** **** ******* ***
***********
**********
*******************
*************
***
********
****
*******
*** **************
REFERENCES
************************
**********************
***
INTEGER
****
*******
KEY
(borrow_transaction_id)
**********
******************************************
*************
***
************
No
***** ****** Fined
****************
***
************
No
Amount
********
****
*****
notes
No ************ No
To
***
notes
******** ***
*********
No
****
*****
****
of Deletion ********
***
*********
No
**** *****
****
**
***********
*********
***********
*** ************ ***
For
********
********
*******
**** **
*********
*** *******
entry
** DB
**************
***** ************
*****
******* ******
************
******************
********************
***
************
*******************
*******************
****
**
****
*****************************
*** ************ ********** fined_transactions **** ** *****
***************************
***
************
********
*******************
Zero
**
*****
******
********** ****** ***** ******* key **** type
***
NULL
********
librarian_id
****
INTEGER
Yes
*******
*** ***************
*****
No
************
***
Librarian
*****
user_name
***
************
***
User
****
assigned
**
**********
password *** ************
***
********
***
******
***********
***
VARCHAR(40)
***
***
******** auditing *******
****
**
librarian
who
******* entry
**
DB ***********
No
************ ***
***
internal ********
*******
name of
*********
***
*******
*****
in
***
********
***
DATETIME
No
****
**** ** ********* ******** *** DATETIME No
Date
****
of
updating
*******
No
BOOL
***
**
********* ** active ** not inactive_reason ***
VARCHAR(40)
No
***
***
*******
**************
Name
************
type Parent
Child
************
*****************************
Non
************
**********
*******************
****
Or
*****
*****************************
Non
************
librarian
*******************
**** ** More
*********************
Identifying
**********
membership
****
**
More
Table:
********
Column name *******
key
****
*****
***
NULL ********
**********
**** INTEGER
****
******* ***
************
************ No
********
**** ID
For
*******
use
************
***
************
No
Either
***
*****
**
student member_name No
************
***
****
of
*******
*************
***
INTEGER
***
******* number of member *******
***
************
***
****
**
Female
******
***
************
No Email ******** ******* No
BOOL
***
******
**
inactive
created_by
***
************
No
*** ******** auditing ******* name
**
*********
***
*******
*****
**
***
updated_by
***
************ *** *** internal ********
*******
name
**
*********
***
*******
*****
**
DB
******** No ********* No ****
time
**
creation
Updated
***
*********
***
****
****
of ********* *************** ***** ************
*****
*******
Child
************
***************************
***
Identifying
members
*******************
*** **
More
***************************
***
************
********
*******************
****
**
More
******************* ************ members *********** *******
One
In
*******
******
model
**
***
***
the
all ******** ***** **** *****
constraints
on
them
***
their
**********
**************
Tables
in
******** *** designed ** *** ***** ****** **** (3NF) ** ** ****
****
to
***
*****
normal
****
***
table
must
******* *** *********** ** ***** ****** form ((1NF) and ****** normal ****
(2NF)Most
of
***
******
in
********
****
****
********
to keep ************ **
mind
We
***
*************
**
******
******
delete
*****
****** ********** * ****
****
***
*********
way
to
normalize
**
****** I
****
*********
only
***
*****
******
**
*****
table
before **************
********
Book_title
********
***************
***************
Publisher ************ ****
**
************ First
******
****** *
****
**
*****
****
there
should ** **
********
******
against
any *****
of
*
*****
and
I
have to ******
*
********
*****
*** ****
***
**
related
data
****
****** ******* ***
**
*****
first
normal
**** *
****
some *******
**
above *****
*
******** ***********
*****
**** ***
******
******
****
&
****
name)
***
********
book_id
**
************
******** Book_title Edition
***************
***************
**********
Author_Fname
*************
2NF
***
2nd
Normal
**** *** non key ********** should **
*****
*********
**
*********
*******
***
partial
**********
** ***
********
*****
*
*******
***
******
****
*****
******
**
associate
with
repeating
*****
of
data *** ******
****
****
*******
***
After this
*
**** to
*****
that each
non-key
***** in
the
***** ****
be
**
*********
****
*********
****
*******
****
*****
******
********
*********** Edition ****************
Publisher
******
Table
********
*************
*************
By
using
2NF
two ****** ******* ***** table
****
*****
detail
***
**
******
******
as
**
1NF and ****** ***** **********
****
*****
table
none
**
no-key
element
**
*********
********* ** ******* **** ****
The
final
*************
**
my
*****
**
3rd
******
**** ** *******
****
normal
****
*
****
**
*********
***
*******
****** ***** are ***
*****
************
*********
on
*******
***
**
the
******
Author ****** **
******
****
**
not
********
*********
**
****
**
** * ******** ***** is
created
***
**
with
its
***
primary
key
***
********** *********** lastname *** ******* *****
12
******
*******
Book_by_author
Table:
The
************
*******
****
*** ****** table ** maintained ** *********** ***** which ** many to
****
**
****
*
book
***
****
*******
and **
******
can
*****
many
*****
****
********
is established
**
******** a
*****
table
***********
*****
****
**** to ******
and
****
*****
**** *****
contains
***
******
****
as
******* **** *****
refer
**
book
*** ******
*****
************
to
establish
one
** **** ************
**
both
******
********
********** *****
***
***** ****
Books
****** *********
**
** used
as
******* ****
********
***********
*******
********
*************
Noof
*******
******
active
*****
****
**** **** ****
***
*****
**
****
****
**
*******
***
category
***** **
**************
table
***
**********
**
*********
from
**************
*****
to ***** ********** ********** ***
*********
**
*****
*********
table
from
*****
*****
**
***** **** ***********
fined_transactions
*****
**
kept
********
****
*********************
*****
**
***** ** minimize *********** Physical ******** ******* After ******* design ** ********
I
***
data
**
********
**
******
*****
***
***** ********** * *** ********* ******* in ***** ********* /* Create ********
**************
*****************
*/
CREATE
********
********************
**
Add
*****
******************** ------------------------*/ ****** TABLE
*****
**
*******
*******
***
*****
book_title
************
edition
************ ******** varchar(40) pub_date
Datetime
*************
********
state
************
**************
integer
row_number
********
created_by ************ ********* ************ ******* ************
*******
************
**********
********
*******
***
**********
);
/*
*** ***** author*/
******
*****
******
**
*********
*******
***
***** **********
varchar(40)
*********
************
*******
*******
NOT
NULl);
**
***
***** *********************************
------------------------------------------
***
******
TABLE
******************
( ********************* *******
***
*****
************
integer ***
*****
book_id
*******
***
*****
********* integer ***
*****
*****************
datetime
returned_datetime
*********
***********
************
returned_by
************
******** ********* **********
************
updated_by
************
*******
************ *******
************
********** *********************
PRIMARY
*** (borrow_transaction_id)
***
/* ***
Table
************************ ******************************
*/
******
*****
*********
**
************
*******
***
NULL
name
varchar(40)
********* varchar(40) ********
************
**********
varchar(40)
**********
************
*******
varchar(40)
*******
************
*************** varchar(40) CONSTRAINT ************
*******
***
(librarian_id)
***
**
Add
*****
“member”
---------------------------*/
CREATE ***** ******* ** *********
*******
***
NULL
***********
********
***********
varchar(40)
***********
************
************ integer ******
************
*****
************
**********
************
**********
************
*******
************
updated varchar(40) ********** **********
*******
***
************
***
**
***
*****
“membership”
***********************************
****** ***** **********
**
************
integer
***
NULL
*********
integer
***
*****
****************** *********
registred_by
************
**********************
*********
*******
***
(librarian_id
member_id)
***
** *** *****
*********************************
***************************************
***
CREATE
*****
******************
**
********************
integer
*** ***** member_id integer *** ***** librarian_id integer *** ***** ********************* integer
NOT
*****
************
************
***************
varchar(40)
*****
************
deleted
************ ******* ************ ********** ************ ********** ********************* ******* *** *********************** *** **
*******
***
************
******************************
*/
*****
*****
******************
***
********** ************************* ******* ***
*********
REFERENCES
*****
***********
ALTER
*****
******************
***
**********
***************************** ******* *** **************
**********
*********
****************
ALTER
TABLE
******************
ADD
********** ***************************
*******
***
***********
**********
*******
*************
*****
TABLE
**********
*** **********
*********************
*******
KEY
**************
REFERENCES
********* **************** ALTER
*****
membership
***
********** *******************
*******
KEY
***********
**********
*******
(member_id); ALTER TABLE
******************
***
**********
librarian_fined_transactions
*******
KEY
**************
REFERENCES
*********
**************** *****
TABLE
******************
ADD
CONSTRAINT borrow_transaction_fined_transactions ******* *** *********************** REFERENCES ******************* ************************* ALTER ***** fined_transactions *** ********** *************************** FOREIGN *** *********** REFERENCES *******
*************
*****
*****
******
***
********** ************* ******* *** (book_id) ********** ***** *********** **** ************ ******** (DML) ********* a family ** ******** ********* **** ** ******** ******** and/or database ***** ** ****** delete and update **** ** * ******** ********* the **** ******* **** manipulation language is **** of *** ***** ** **** ** ******** *** manipulate data ** a ********** **************** ********* queries
***
used ** ****** ****** *** ****** **** **** ************** few ******* only): ****** INTO author (author_id first_name
*****************
VALUES
(2006
**************
********************* ****** **** ***** ******************
edition
num_of_copies
*****
catalog_number
********************
update_by
*******
********
******
**
*************
*************
**
*******************
3
5'saira'
***************
'2010-10-12
*************
***************
12:10:03'); ****** **** *************** (author_id ******** ****** (2006 **** ****** INTO categories ************ ************** VALUES ***** ********************** ****** **** fined_transactions ********************* ********* librarian_id ********************* ************ *************** notes ******* created created_by) ****** (1 4 2 1 *********** '0' 'no ********* ******************** *************** ************* ****************** ****** ***** *** ********** = 'MCIP' ***** ******** 3; ****** from ***** ***** ********** *************** ************* ** *** *********** ** ***** ********* copies of * specific
*****
------------------------------------------------------
*******
(booksnum_of_copies
-
tempborrowed_books_count)
**
*****************
FROM
******
****
JOIN
********
********
**
*********************
****
*******************
*****
book_id
*
1
*
**
*****
** ************ = 1 ***** booksbook_id * *** /*To **** books **** overdue ***** ********************************************* SELECT ******************* **************** ************************************ *************************** FROM
*******************
**** JOIN ******** ** *************************** = ***************** **** JOIN ****** ** ************************* * booksbook_id ***** Date_due ***** ********************* ******** fine ******* ******* ******** ******************************************************** ******* ***************** ******************* ****************** * ********************* *************** FROM members **** JOIN ******************* ON *************************** * ***************** ***** ** membersmember_id
HAVING
****************** *
*********************
****
/*Some
*****
**********
******************************
*******
member_id
librarian_id
*************
FROM
*******************
*****
************
**** ***
*******
************
member_name
****
********
*****
***********
**
'student';
*******
************ ************ gender FROM members ***** BY *********** ****** ******* book_title pub_name **** books ***** ******** LIKE ********************* Conclusion The **** conclusion **** can ** ***** ** ********* that using MySQL database system *** ****
********
***
*******
*******
which
***
** used ** ***** **** regarding ***** ******* ** library **** *** **** ****** ****** *** add *** records **** database ** designed **** **** flexibility **** ** *** ** *********** ** *** *******
**
*******
it
***
** ********
easily
into *** ********** ** *** *** ** **** **** as ********* ********** ****** * * ****** R * Bailey S A ******* * *
*****
*****
*
*
(1987)
***
********
********
libraryIEEE
************
25
Connolly
*
*
&
****
C
E
**************
systems:
*
********* ********
to
****** implementation and management Pearson ********** ****** K A (2005) ******* ******* **** ********* ******* database ***** dataLibrary Hi ********* *******
