Help in T-SQL Database Design

I need ERD diagrams for 2NF and 3NF forms for this database and queries written in T-SQL for the 5 questions given in the word file. Transactions table must be added (and some columns as necessary)
Here is an initial SQL script:
USE [Midterm]
GO
/****** Object:  Table [dbo].[InitialData]    Script Date: 3/20/2018 6:26:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InitialData](
      [BuyerFirstName] [nvarchar](50) NOT NULL,
      [BuyerLastName] [nvarchar](50) NOT NULL,
      [BuyerAddress] [nvarchar](200) NOT NULL,
      [BuyerCity] [nvarchar](50) NOT NULL,
      [BuyerState] [nvarchar](50) NOT NULL,
      [BuyerZip] [nvarchar](50) NOT NULL,
      [BuyerDOB] [date] NOT NULL,
      [BuyerPhoneNumber] [nvarchar](50) NOT NULL,
      [BuyerEmail] [nvarchar](50) NOT NULL,
      [BuyerDriversLicense] [nvarchar](50) NOT NULL,
      [SellerFirstName] [nvarchar](50) NOT NULL,
      [SellerLastName] [nvarchar](50) NOT NULL,
      [SellerAddress] [nvarchar](200) NOT NULL,
      [SellerCity] [nvarchar](50) NOT NULL,
      [SellerState] [nvarchar](50) NOT NULL,
      [SellerZip] [nvarchar](50) NOT NULL,
      [SellerDOB] [date] NOT NULL,
      [SellerPhoneNumber] [nvarchar](50) NOT NULL,
      [SellerEmail] [nvarchar](50) NOT NULL,
      [SellerDriversLicense] [nvarchar](50) NOT NULL,
      [HorseSaleSlipNumber] [int] NULL,
      [HorseSaleTagNumber] [int] NULL,
      [HorseSaleBreed] [nvarchar](50) NULL,
      [HorseCogginsBloodTest] [bit] NULL,
      [HorseType] [nvarchar](200) NULL,
      [CattleSaleSlipNumber] [int] NULL,
      [CattleDescription] [nvarchar](50) NULL,
      [CattleTagNumber] [int] NULL,
      [TackSaleSlipNumber] [int] NULL,
      [TackDescription] [nvarchar](200) NULL,
      [TackLotNumber] [int] NULL,
      [TackUsed] [bit] NULL,
      [TackNew] [bit] NULL,
      [CommisionCharges] [money] NOT NULL,
      [InsuranceCharges] [money] NOT NULL,
      [VetCharges] [money] NOT NULL,
      [LaborCharges] [money] NOT NULL,
      [SalesTax] [money] NOT NULL,
      [YardageCharges] [money] NOT NULL,
      [UtilitiesExpense] [money] NOT NULL,
      [OfficeExpense] [money] NOT NULL,
      [TaxExempt] [bit] NOT NULL,
      [CashPayment] [money] NOT NULL,
      [CheckPayment] [money] NOT NULL,
      [CustomerBalance] [money] NOT NULL,
      [CustomerDeposit] [money] NOT NULL,
      [CustomerPaid] [bit] NOT NULL,
      [CheckVoided] [bit] NOT NULL,
      [CheckClearedBank] [bit] NOT NULL,
      [VetPaid] [bit] NOT NULL
) ON [PRIMARY]
GO
Mikheil ChkheidzeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It is not possible to create an entity relationship diagram from a single, denormalized table only.

For example it is not possible to tell to which entity or relation TaxExempt belongs to.

Another example: while buyers and sellers can be different entities (two tables), they can also be one entity (persons) with a discriminator. Or they can be three entities with subclassing (persons->buyers, persons->sellers).
You table does no contain enough information to make this decision.
0
Mikheil ChkheidzeAuthor Commented:
Yes, there is no one correct answer to this. You could follow just one of your interpretations. Any help would be appreciated.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Curiosity overwhelms me ... what class is this homework for?  Also I don't see 'the word file.' in this question.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mikheil ChkheidzeAuthor Commented:
Midterm.docx DATABASE_AND_WEB_PROGRMNG, I have attached the file to this comment as well.
0
Mikheil ChkheidzeAuthor Commented:
The hint was also given that I need to create transactions table with date of transaction, buyerID, SellerID, HorsesaleSlip#, CattleSalesSlip#, TackSalesSlip#, basically, a table of IDs, and the seller and a buyer are both customers.  A seller can also be a buyer. This business is a place where you can sell as well as buy stuff.
0
Mikheil ChkheidzeAuthor Commented:
Also, here is data inserted into the 1NF database. script.sql
0
ste5anSenior DeveloperCommented:
*cough* The document describes all requirements you need to know. Thus you need create your ERD from the document. Not from the table.
0
Mikheil ChkheidzeAuthor Commented:
Yes, and that's where I am stuck at. Any help?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Any help?
Have you done anything so far?  Since this is obviously for school posting your assignment and asking 'Any help?' is not abundantly motivating for experienced experts to flat-out do your midterm for you.   So show us that you have some thoughts so far, and we'll help educate you on what you need to consider.  

But just asking 'Any help?' without any thoughts ... no thanks.
1
Mikheil ChkheidzeAuthor Commented:
If you could provide at least some starting point or the part of the solution, it would be helpful. I am basically sketching the general relationships and looking for the ways to the solution. I think for the first and second queries I need to check whether customer balance is less than or more than 0. if it is less than 0, that is the case when the business owes money to the customer, and if it is more than 0, they owe money to bussiness.  For the 3rd question I am thinking about using variables and Datediff function and querying against transactions table that I will create and add "date of transaction" column to it. For the 4th question, I will also need to add totalSalesVolume to the transaction table and write a query against it. I have no idea about the 5th query. I will post further progress on this here. Again, I am not asking for the whole solution, just any help.
0
Scott PletcherSenior DBACommented:
This must be done in stages / phases / steps.  You can't just skip ahead to tables and queries.

The first step is a logical design, which could include " ERD diagrams for 2NF and 3NF forms".  Note that you can't get the 3NF diagram without first doing the 2NF diagram, as you have to reach 2NF first (3NF is defined as 2NF plus additional checks).  In this logical design, physical things do not exist: no tables, indexes, etc..  Instead there are entities, attributes, relationships, etc, as would appear in an ERD (hence "Entity Relationship Diagram").

Once you've completed 3NF, you convert the ERD/logical design into a physical db definition.  That is, you create actual tables, columns, designate keys, other indexes, etc..

Finally, after all that, you can code any query(ies) you need.
0
Scott PletcherSenior DBACommented:
I know you've been given InitialData in the form of a table, but that's for convenience only.  Basically just consider every column a separate attribute (logical piece of information).  In the real world, you'd find out what info is needed from business interviews, forms, etc..  In this case, you can't do all that, so the instructor did that for you and gave you the final results, a list of all relevant pieces of data that need to go into the logical model and then into tables.  The instructor also provided the data already in 1NF, so can even skip worrying getting the data into 1NF.

Using that base info, you have to determine what entities should exist. For example, "Buyer", "Seller", "Horse" and "Cattle" seem pretty obvious entities (things about which you need to keep data).  Probably "Tack" (gotta admit, I don't have any knowledge of "Tack" in this context, as I ain't no rancher.)  And so on.

Then, assign each attribute to an entity.  You do that by going thru the normalization process, i.e., you make the data conform 2NF, then 3NF.  As part of hat, you'll be assigning identifying/key column(s) for every entity: without that, you can't properly identify 2NF and 3NF.

You may hit an attribute / InitialData column that you're not sure of.  Don't worry, skip it and come back.  Once you get more entities laid out, the attributes will usually fall into place.
0
ste5anSenior DeveloperCommented:
I would start with the given document. Numerate the text, paragraphs and sentences. Then boil down the information. Identify subjects, verbs and objects. Paragraph one §1 contains only the introduction, thus can be ignored. Further more, the given inital data and the five query describitions can be used also, but only after you worked thru the normal text.

§2.1:
Customer data is stored for each transaction and their can be more than one customer per sale.
=> Transactions are stored for customers. Sales have one or more customers.
=> Sales have one or more customer (assuming Transactions = Sales)
=> Entities: Sales, Customers. Relation "has a". Cardinality 1:n. More graphically: Sales <-1- have -N-> Customers

Do this for the entire text. Due to the type of exercise, you need to derive mostly all attributes later from the initial data.
Now can draw the entire ERD.
After that you derive your tables from the ERD. Which are then normalized. And now you can provide the queries.
0
Scott PletcherSenior DBACommented:
OK, gotta admit, I didn't read thru the document.  The document has descriptions of data that is not in 1NF, and the document makes clear that the 1NF step is required; the "table" structure you posted does not.
0
Mikheil ChkheidzeAuthor Commented:
I was actually given an excel sheet with columns which had multiple values, such as multiple names in one cell, but converting it to 1NF was an easy part and I did not post it here. Thanks to everyone for the kind help.
0
Mikheil ChkheidzeAuthor Commented:
2NF 2NF Diagram 3NF Diagram 3NF
- This is what I came up with so far. Are there any obvious mistakes?
0
Scott PletcherSenior DBACommented:
You've made some good progress!  But, yes, there are some mistakes, as to be expected.  After all, normalization is a complex process that requires practice.

Consider a few random things (I don't have much time now):

1) A Horse exists outside of (before and after) it is part of a sale.  That is, the horse's base id should not be based on a sale, but on the horse itself.  The same would be true for cattle, of course.

2) A driver's license number must include the state first to have any real meaning.

3) Don't overuse nvarchar (which, btw, in a logical model, should be indicated differently, since nvarchar is a physical property).

4) You still have a 1NF issue on Transactions, IIRC.  Multiple buyers per tran are possible.  In fact, the entire Transaction entity has become kind of a "quick dump".  You need to make a transaction more discrete.  In fact, consider carefully if typical expenses -- utilities, office supplies, etc. -- should be lumped in with livestock sales in the same table.  Almost never do sales to clients get combined with routine back office expenses (not least for security reasons, but that's more involved than this discussion).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.