+1 (208) 254-6996 essayswallet@gmail.com
  

Imagine that you work for a data service company that specializes in data storage, data processing, and data streamlining for public libraries. The current implementation is a file-based system with all data stored in a single spreadsheet. As the company expands the business to serve more libraries, it becomes evident that the current model does not meet the ever-increasing demand.
You have been assigned the task of designing a new ORACLE relational database to replace the current file-based storage. It will be a single centralized database to store information needs for all libraries. Some key information to be stored includes data about customers, libraries, catalog, physical items, and check-in/out transactions.
Additional general business background information is listed below:
a. Each library system operates independently. Each library system issues its library card to its customers.
b. A customer can have multiple library cards, one for each library system. For example, a customer can have both a Baltimore library card and an Annapolis library card. Different library systems may issue cards with the same number.
c. A library has many branch locations. Customers visit a branch to check in/check out items.
d. Different libraries share generic catalog information. Currently there are two types specific catalog items – book and DVD.
e. Each branch stores and maintains physical items. General information about each physical item can be found by checking the catalog. There could be multiple copies for the same title, so each copy needs to be stored separately. A branch uses a copy number to identify a particular copy so that to keep track of information such as purchase date.
You were provided with a sample data stored in the current spreadsheet format as attached. Please be aware that it is possible that a customer has never checked out any item (as shown line 23), and it is possible that an item has never been checked out yet (as shown line 24 through 67).

Sheet 1

TRANSACTION_UNIQUE_IDENTIFIERCHECKOUT_DATEDUE_DATERETURNED_DATECUSTOMER_UNIQUE_IDENTIFIERCUSTOMER_FIRSTNAMECUSTOMER_LASTNAMECUSTOMER_STREETCUSTOMER_CITYCUSTOMER_STATECUSTOMER_ZIPLIBRARY_CARD_UNIQUE_IDENTIFERLIBRARY_CARD_NUMERCARD_PINCARD_EXPIRATION_DATECARD_ISSUE_LIBRARYLIBRARY_CONTACT_PHONELIBRARY_ADDRESSCATALOG_ITEM_UNIQUE_IDENTIFIERTITLEDESCRIPTIONPUBLISHERRELEASE_DATETYPEBOOK_ISBNBOOK_PAGESDVD_LENGTHBRANCH_ITEM_UNIQUE_IDENTIFIERBRANCH_ITEM_COPY_NUMBERBRANCH_ITEM_PURCHASE_DATEBRANCH_NAMEBRANCH_PHONEBRANCH_ADDRESSBRANCH_UNIQUE_IDENTIFIERBRANCH_LIBRARY_SYSTEM
1Feb/02/2017Mar/02/2017Feb/28/20171ChristianWalker96 Clark DriveHicksvileNY118011167772161234Dec/31/2019New York Public Library212-555-1234476 5th Avenue, New York, NY1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,28011Feb/01/2011125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
2Feb/28/2017Mar/28/2017Apr/01/20171ChristianWalker96 Clark DriveHicksvileNY118011167772161234Dec/31/2019New York Public Library212-555-1234476 5th Avenue, New York, NY1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,28022Mar/15/2012125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
3Apr/01/2017May/01/2017Apr/03/20171ChristianWalker96 Clark DriveHicksvileNY118011167772161234Dec/31/2019New York Public Library212-555-1234476 5th Avenue, New York, NY3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28131Jul/08/2011Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
4Apr/03/2017May/03/2017Apr/30/20171ChristianWalker96 Clark DriveHicksvileNY118011167772161234Dec/31/2019New York Public Library212-555-1234476 5th Avenue, New York, NY8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01203Feb/23/2013Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
5Mar/03/2017Apr/03/2017Mar/28/20174EmileCross387 Circle AveVincentownNJ080882335544328192Jul/04/2020New York Public Library212-555-1234476 5th Avenue, New York, NY1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,28011Feb/01/2011125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
6Dec/12/2017Jan/12/20184EmileCross387 Circle AveVincentownNJ080882335544328192Jul/04/2020New York Public Library212-555-1234476 5th Avenue, New York, NY2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,32831Mar/04/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7Dec/12/2017Jan/12/20184EmileCross387 Circle AveVincentownNJ080882335544328192Jul/04/2020New York Public Library212-555-1234476 5th Avenue, New York, NY2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,32842Mar/04/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
8Jul/14/2017Aug/14/2017Aug/20/20172DeenaPilgrim838 Orange StreetFrederickMD217013104857608888Feb/29/2020Baltimore County Library443-555-3456320 York Road, Towson, MD1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,280211Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
9Aug/20/2017Sep/20/2017Sep/19/20172DeenaPilgrim838 Orange StreetFrederickMD217013104857608888Feb/29/2020Baltimore County Library443-555-3456320 York Road, Towson, MD1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,280222Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
10Sep/19/2017Oct/19/2017Sep/21/20172DeenaPilgrim838 Orange StreetFrederickMD217013104857608888Feb/29/2020Baltimore County Library443-555-3456320 York Road, Towson, MD7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722291Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
11Sep/21/2017Oct/21/20172DeenaPilgrim838 Orange StreetFrederickMD217013104857608888Feb/29/2020Baltimore County Library443-555-3456320 York Road, Towson, MD8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01421Sep/15/2014Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
12May/12/2017Jun/12/2017May/31/20173CalistaSecor9330 El Dorado LaneTemple HillsMD207484209715200911May/17/2021Baltimore County Library443-555-3456320 York Road, Towson, MD8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01421Sep/15/2014Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
13May/12/2017Jun/12/2017May/31/20173CalistaSecor9330 El Dorado LaneTemple HillsMD207484209715200911May/17/2021Baltimore County Library443-555-3456320 York Road, Towson, MD6Moonrise KingdomDrama ComedyFocus FeaturesMay/25/2012DVD1:34411May/15/2015Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
14May/12/2017Jun/12/2017May/31/20173CalistaSecor9330 El Dorado LaneTemple HillsMD207484209715200911May/17/2021Baltimore County Library443-555-3456320 York Road, Towson, MD5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08401Dec/12/2016Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
15Nov/11/2017Dec/11/2017Dec/09/20175JohnnyRoyalle9948 Campfire DriveEast ElmhurstNY113696327682568008Mar/11/2020UMUC Library240-684-20201616 McCormick Dr, Largo, MD5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08511Jul/11/2017UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
16Dec/09/2017Jan/09/20185JohnnyRoyalle9948 Campfire DriveEast ElmhurstNY113696327682568008Mar/11/2020UMUC Library240-684-20201616 McCormick Dr, Largo, MD3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28451Sep/09/2012UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
17Dec/12/2017Jan/12/2018Dec/20/20175JohnnyRoyalle9948 Campfire DriveEast ElmhurstNY113696327682568008Mar/11/2020UMUC Library240-684-20201616 McCormick Dr, Largo, MD5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08511Jul/11/2017UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
18Jul/14/2017Aug/14/2017Jul/20/20174EmileCross387 Circle AveVincentownNJ080887163845128192Feb/15/2020UMUC Library240-684-20201616 McCormick Dr, Largo, MD5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08511Jul/11/2017UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
19Jul/21/2017Aug/21/2017Jul/22/20174EmileCross387 Circle AveVincentownNJ080887163845128192Feb/15/2020UMUC Library240-684-20201616 McCormick Dr, Largo, MD8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01541Dec/17/2015UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
20Oct/12/2017Nov/12/2017Oct/22/20174EmileCross387 Circle AveVincentownNJ080882335544328192Jul/04/2020New York Public Library212-555-1234476 5th Avenue, New York, NY2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,32831Mar/04/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
21Oct/10/2017Nov/10/2017Oct/22/20174EmileCross387 Circle AveVincentownNJ080882335544328192Jul/04/2020New York Public Library212-555-1234476 5th Avenue, New York, NY2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,32842Mar/04/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
6HarleyCohen96 Vine StreetBowieMD207155655361284711Oct/09/2019UMUC Library240-684-20201616 McCormick Dr, Largo, MD
1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,280331Mar/03/2011Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,280431Sep/11/2011UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
1Way of KingsFantasyTor BooksAug/31/2010BOOK97807653652791,280551Mar/08/2016McKeldin Library301-405-9046UMD, College Park, MD302UMUC Library
2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,328231Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,328242Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,328341Aug/19/2014Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,328441Jan/12/2015UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
2Words of RadianceFantasyTor BooksMar/04/2014BOOK97807653652861,328561Jul/12/2016McKeldin Library301-405-9046UMD, College Park, MD302UMUC Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28142Jul/08/2011Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28153Jul/08/2011Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28251Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28351Aug/19/2014Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28362Sep/21/2015Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28373Sep/21/2015Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28462Sep/09/2012UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28473Jul/14/2014UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
3InceptionScience Fiction/ThrillerWarner BrosJul/08/2010DVD2:28484Oct/19/2016UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,07951Feb/01/2011125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,07962Feb/01/2011125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,07973Oct/09/2015125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079261Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079381Nov/25/2014Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079392Feb/23/2015Woodlawn Branch443-555-56781811 Woodlawn Dr, Woodlawn, MD202Baltimore County Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079491Jan/20/2013UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079502Feb/05/2015UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
4Infinite JestTragicomedyBrown, Little, and Co.Feb/01/1996BOOK97803160665251,079571Dec/01/2016McKeldin Library301-405-9046UMD, College Park, MD302UMUC Library
5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08161Mar/01/2017Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
5La La LandMusicalSummit EntertainmentDec/09/2016DVD2:08271Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
6Moonrise KingdomDrama ComedyFocus FeaturesMay/25/2012DVD1:34171Dec/12/2013Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
6Moonrise KingdomDrama ComedyFocus FeaturesMay/25/2012DVD1:34281Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
6Moonrise KingdomDrama ComedyFocus FeaturesMay/25/2012DVD1:34521Jul/11/2017UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK978075640407972281Feb/01/2011125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK978075640407972292Apr/15/2013125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722103Dec/20/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722114Dec/20/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722125Dec/20/2014125th Street Library212-555-2345224 E 125th St, New York, NY101New York Public Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722302Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722313Jun/14/2017Essex Branch443-555-45671100 Eastern Blvd, Essex, MD201Baltimore County Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722531Apr/09/2016UMUC Library Largo240-684-20201616 McCormick Dr, Largo, MD301UMUC Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722581Jan/14/2017McKeldin Library301-405-9046UMD, College Park, MD302UMUC Library
7The Name of the WindFantasyDAW BooksMar/27/2007BOOK9780756404079722591Jan/14/2017Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01181Oct/01/2011Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
8The Social NetworkDramaColumbia PicturesOct/01/2010DVD2:01192Oct/01/2011Grand Central Library212-555-3456135 E 46th St, New York, NY102New York Public Library
8The Social NetworkDramaColumbia PicturesOct/01/201