components of a database management system (DBMS), namely:

• the entity relationship diagram (ERD) which serves as the blueprint for your overall database design
• database tables and metadata which serve to both house your data (the table) and define the unique characteristics of each data element (the metadata for each

data element) within the table
•   database queries, forms, and reports
o   queries are formalized questions or “calls” employed ad hoc to ask questions of the database in line with your business needs
o   forms are employed either to view data in a set format or as an more use-friendly interface for data entry or querying
o   reports are formalized ways of presenting data housed in a database, usually in line with some routine and regular need, such as a monthly bill to the

customer, or the end of semester transcript to the student.

The case that follows provides a business context for the three assignments where we will explore the construction of an ERD, data tables, and queries, forms, and

reports respectively. On Blackboard both in the “General Information Pages” and the “Session Assignment” pages within the Course Materials folder, you will find

helpful readings and slide sets that explore these topics and discuss in a limited way, how to create ERDs, tables, queries and the like, either manually or in

Microsoft Access. However, there are any number of other additional sources that you may find for yourself and for the use of your team, employing Wikipedia, Google

search, and the HELP function in Access and Excel to name just a few sources. You may also want to reach out to your peers through the course’s discussion forum for

further assistance. do not limit yourself; explore!!!

The Case Study:

Sammy’s Beverage Emporium is a thriving retail business, with annual gross sales of $900,000 per year, located on Columbus Avenue in Boston, Massachusetts not far from

Northeastern University’s (NEU) main campus. The store specializes in a wide range of popular beers, wines, and snack foods, as well as cigarettes, cigars, and a more

limited range of hard liquors. These products include hundreds of brand names and approximately 15,000 unique skew numbers, coming from many different suppliers.

The store is known for its great variety of readily available, quality products sold at fair prices. Sammy has managed the store for years but finds that with the

growing diversity of Northeastern’s student population, he can no longer keep up with the range of products that he must maintain to address the needs of his

customers. He has therefore asked you to create a database management system to assist him in the organization and use of the data that drives his business.

This database system will be used to track the total inventory of the Emporium’s products to both fill orders for customers and to reorder product from suppliers so as

to always have popular products on hand for Sammy’s thirsty customers and compulsive smokers.Note that this data management system will not replace the existing point

of sales system that manages all payments and the adjustment of inventory counts based on sales. The typical customer orders a mix of items (drinks, smokes and

snacks) – either as a walk-in or call-in order. Orders may be picked-up by the customer at the store or delivery to the customer’s residence. Students living in

University housing as well as Northeastern academic and administrative departments place orders with Sammy’s. Student purchases tend to be for individual bottles of

wine and liquor, and six-packs of beer, whereas NEU office functions tend to include larger orders for cases of wine and beer. As part of the valued-added service

offered by Sammy’s, employees provide recommendations based on the particular tastes and interests of Emporium customers. Customers pay in cash or by Visa or

MasterCard for their purchases.

The Emporium’srelations with the NEU administration and the greater Columbus Avenue community are quite good. To keep it that way, Sammy’s is scrupulous in ensuring

that the store complies with all State and Federal laws concerning the sale and consumption of alcohol and tobacco-based products. Any customer who wishes to place

orders with the store must therefore establish an account through a personal visit to the store where he/she provides proof of age, credit worthiness, and the like.

Only employees twenty-one years of age or greater are allowed to fulfill orders or to make deliveries from the store.

Your Assignments:

Your first task is to create an entry relationship diagram that will identify each entity (a.k.a. table) required by the database as well as the relationships between

entities, their primary and foreign keys, and any other attributes (a.k.a. data elements) associated with that entity. Next you will design each table of the

database, deciding on the order of the data elements within each table and their own detailed attributes (a.k.a. metadata). Lastly, to make the database useful to

Sammy, you will design a set of queries, two forms and at least one report that he will employ in his day-to-day use of this new data management platform.
In all, you have estimated that this assignment will require 10 to 15 hours of your time and that you wish to be compensated in product drawn from Sammy’s store upon

completion of your work. You are both very pleased with this arrangement in that it provides Sammy with an information management resource that he desperately needs

to run his business and that it provides you with some practical hands-on experience in data management.

The Assignments:
The ERD (sessions 3 and 4)
1.   create an ERD
a.   to include all the major entities of Sammy’s business model.
b.   to include the relationships (i.e. one-to-one, one-to-many, or many-to-many) among the entities.
c.   each entity must show a primary key and any foreign or secondary keys if appropriate.
d.   each entity must show at the least all of the most significant attributes as required by the business. For the sake of simplicity, these attributes may be

clustered (e.g. you can show “address” as an attribute whereas in the actual database “address” will be comprised five to six data elements.
e. include a drawing “key” or legend and perhaps other relevant information – such as assumptionsto enhance the reader’s understanding of the ERD. Remember one of

the first uses of the ERD will be to review it with Sammy to ensure that you have understood his business needs correctly and fully. You want everything to be both

correct and clear with nothing left in the unsaid.
2.   in creating the ERD remember these basic rules of thumb:
a.   the business itself is not an entity but the entities within the business (i.e. people, things, events) may require their own table(s)
b.   look for relationships between entities
c.   choose good data element (a.k.a. field) names
d.   break down your information (e.g. address is typically 5 data elements)
e.   include all details in one place (i.e. group data by entity)
f.   for each entity – look for and define its attributes
g.   avoid duplicate information
h.   avoid redundant information
i.   include an ID (primary key) field (note: two entities might employ the same primary key)
j.   include referential foreign (a.k.a. secondary) keys for an entity if appropriate
k.   do not include a field if it may be calculated from other fields
3.   be sure to follow the following design standards in creating the ERD:
a.   use a rectangle for each entity
b.   use a diamond for the active verbs that describe the relationships between entities.
c.   use a single line from the entity to individually connect each attribute to its entity.
d.   be sure to differentiate primary and foreign keys form other entity attributes
e.   remember that a line, an arrow, or a double appropriate connects each entity to its verb.
f.   verbs may also at time include attributes
g.   be sure to use the letters n, m or the infinity sign (meaning many) to this case to indicate a “many” relationship
4.   When you are done and before the due date posted in the Assignment Repository in Blackboard.

The Database Tables and Metadata(Sessions 5 and 6)
1.   Employ the ERD created previous but also take into account the feedback of the instructor as to what needs to change or be improved in your ERD.
2.   Employ the Database Table Layout Templatein Excel to document each Entity/Table for the Sammy’s Beverage Emporium database.
a.   indicate the primary key (you might require a compound primary key)
b.   indicate any foreign keys if needed
c.   add in the remaining attributes to the table (note: The order of data elements in the table is not random. The order should follow the logic of data

capture/entry as part of the business process. For example, the capture of a customer’s or a supplier’s address follows a certain logical order).
d.   for each data element, include its metadata description.
3.   Remember that a complete metadata description for a given data element may include any and all of the following as appropriate:
a.   definition of the data element (as part of the business process)
b.   data type
c.   number of characters
d.   format
e.   valid values
f.   related business rules

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂