Hi folks, this is my first post in danniweb, please excuse me if I am in the wrong place or the thread title lacks self explanation. I am very disoriented in the field since im new in MS SQL.
Before going into the problem, I'm going to give some context:
As an assigment, I'm required to create a small simple DB in MS SQL 2008 for a store. It is supposed to have 3 entities: Customers, Products and Sales. This is the design I came out with:
*Customer (customerId (PK), name, lastname, birthdate, birthplace)
*Customer-phone# (Customer-phone#Id(PK), customerId(FK), phone-number, phone#Id(FK))
*Phone# ( Phone#Id(PK), number, type )
*Products( productId(PK), name, department, expiration-date, price )
*Sales (saleId(PK), customerId(FK))
*Sales-details( sales-detailId(PK), saleId(FK), date, productId(FK), quantity, totalprice )
I was given four '.txt' files, each one of them have the data that I'm supposed to import into the database. I have one file with names, one file with lastnames, and three more files with product names: 'barsoap.txt' contain product names for soap, the same for 'mouthwash.txt', 'skincare.txt' and 'toothpaste.txt'.
Lets start with the names:
In my scheme, the customer name is divided in two fields: name and lastname. I have firstname.txt and lastname.txt with the data i have to import, the problem is that firstnames.txt and lastname.txt dont have the same amount of records, therefore, if I try importing them using the wizard, I wouldnt have enough names for the exeeding lastnames (I already tried it and thats what happened). My instructor told me I should be able to write a stored procedure that generates ramdom names combining the first and lastnames from the files, but I dont have even a clue on how to do so..
Now with the products:
Now if we take a look back again on my scheme, i got a 'department' field that is supposed to be filled according to the category of the product. Since i have 'barsoap.txt', 'mouthwash.txt', 'skincare.txt' and 'toothpaste.txt', i decided to divide them into two categories" mouthwash and toothpaste would be dental care and skincare and barsoap would be body care. Anyway I have to write another stored procedure that assigns their respective departments to each entry and i dont know how to do it either..
And finally, the sales issue:
This assignment was supposed to be about generating 100,000 ramdom sales using stored procedures, that means filling the sales table with ramdomly selected data from the previous tables (wich are supposed to be previously filled up with the data from the text files).
I would appreciate very much your help, and excuse me again if im not being clear on what i need to do.