Mayville is a rural village with a population of 900. Until now, Mayville was se
ID: 3531565 • Letter: M
Question
Mayville is a rural village with a population of 900. Until now, Mayville was served by a bookmoblic from a larger town. The Mayville Village Council has authorized funds for a small public library, and you have volunteered to set up an information system for the library. Assume that the library will have multiple copies of certain books.
1. Draw an ERD for the Mayville library system.
2. Indicate cardinality.
3. Identify all fields you plan to include in the tables.
4. Create 3NF table designs.
Explanation / Answer
Hey this is the answer Please join the ERDs to create the complete ERD.I have also gave you the complete 3NF Table structure..So please Check it out..And Rate..It is the most appropriate design..
---------------------------------------------------------------------------------------------
Assumsions :
1 : One
1..* : many
---1----*--- One to Many
---1----1..*---- One to One or Many
----1 ----1..X---- One to One or to a Certain Number(like 3)
---------------------------------------------------------------------------------------------------
ER DIAGRAM :-
------------------------------------------------------------------------------------------------
Library -------1----<HAS>------1.. *------Books
Library -------1---<HAS>---1..*-----Members
Members -----* ---- <Registered> ----- 1 ----Library
Member ---- 1 ----< Read> ------- 1..* ------- Books
Member -----1 ---- <Borrow>----- 1..3 ____ Books
--------------------------------------------------------------------------------------------------
Here the entities are Library,Member and Books
-------------------------------------------------------------------------------------------------
TABLE STRUCTURES REQUIRED FOR THIS DESIGN ARE :
-----------------------------------------------------------------------------------------------------
TABLE 1 :
Library Table
Attributes Are :
1 . Name (Primary key)
2. Establishment Date
3. No of Books
4. No of Members
TABLE 2 :
Book Table
Attributes are :
1 . Book ID(Primary Key)
2 . Name
3. Author Name
4. Copy No
5. Avaliablity
6. Added Date
7. IssueTo
8. Issued Date
9. Return Date
10. Price
TABLE 3 :
Member Table :
Attributes Are :
1 . Member Id(Primary Key)
2. Name
3. Date of Join
4. User Name
5. Password
6 Issued Book 1ID(foreign Key reference to Book table)
7. Book1 Issue Date
8. Issued Book2 ID(foreign Key reference to Book table)
9. Book2 Issue Date
10. Issued Book3 ID (foreign Key reference to Book table)
11. Book3 Issued Date
12 . Is Fined
13. Fine Amount