Beginner needing help

Question

Beginner needing help

AXPRRF

SSC Veteran

Points: 267
More actions
July 4, 2020 at 2:53 pm

#3767487
Removing post
- This topic was modified 3 years, 9 months ago by AXPRRF.
July 10, 2020 at 7:34 am

This was removed by the editor as SPAM
January 29, 2021 at 6:40 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

This is either homework or a test of some kind. At least TRY to do something on your own, post it, and if you have a question you can't answer about your own work, then ask it.

I also suggest you read the article at the first link in my signature line below so that when you do actually try something yourself and need some help, people will be better able to help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 2

Did you read the part about posting DDL on SQL forums? Your useless picrure is not RDBMS, but the usual beginner's version of a dimensional schema. One of the basic rules of data modeling is a data element name does not contain metadata, like "_key" or "dim_" because the data element name should tell us what something is not how it is used in a particular instance. Since we have no DDL. We have no idea how these pictures of tables are related. Nor do we have any constraints. Perhaps you should read a book?

CREATE TABLE Personnel

(emp_id CHAR(10) NOT NULL PRIMARY KEY,

emp_first_name VARCHAR(20) NOT NULL,

emp_last_name VARCHAR(20) NOT NULL);

Since the table models a set of entities, its name has to be a plural or collective noun in a valid model. Did you notice that there's a key to the table? And it has to be declared in the DDL not in the name.

CREATE TABLE Customers

(cust_id CHAR(10) NOT NULL PRIMARY KEY,

cust_first_name VARCHAR(20) NOT NULL,

cust_last_name VARCHAR(20) NOT NULL,

cust_phone_nbr CHAR() CHECK (cust_phone_nbr LIKE ...'));

Technically, putting the phone number in with the customers de-normalizes your schema. A customer has a relationship with the phone number I can have more than one or none at all. Also, if you're going to actually store phone numbers you need to have a string of digits that conforms to the international standards for phone numbers and is enforced in the a regular expression.

CREATE TABLE Products

(product_gtin CHAR(15) NOT NULL PRIMARY KEY,

product_name VARCHAR (35) NOT NULL,

unit_price DECIMAL (12,2) NOT NULL);

Products should be identified by an industry-standard encoding; I happen to like the global trade item number (GTIN), but you should use whatever your industry standards are. Prices can be an aggregate, retail, wholesale, or by units or other ways. When naming a data element, be specific. I'm going to assume these are unit prices, and there is some standard of measure in which your products are sold. That's probably wrong, since things like mulch and seeds can come in different packaging but we don't have much to work with here.

Finally, if that was intended to be a table of Transactions, it's completely wrong. A transaction needs a date, a seller, buyer and and a product. Why do you think that "value" is a clear and precise term. It's immediately obvious to anyone reading this? NO! What does a negative value mean? Did you know that the word VALUE is a reserved word in ANSI/ISO standard SQL? A transaction is an activity, so each transaction should have a date. Why did you split things apart?

CREATE TABLE Tranactions

(emp_id CHAR(10) NOT NULL

REFERENCES Personnel,

cust_id CHAR(10) NOT NULL

REFERENCES Customers,

product_gtin CHAR(15) NOT NULL

REFERENCES Products,

transaction_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

PRIMARY KEY (emp_id, cust_id, product_gtin, transaction_date),

...);

Now your little homework problem becomes immediately obvious. You will find the next couple of months. As you learn SQL and RDBMS, that most of the work is done in the DDL and not in absurd pictures of tables from spreadsheets. You will have an epiphany in which you realize that using integers to create 1960s pointer chains is not relational. Currently, your mindset is completely wrong.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Lordy, Joe. The OP didn't design any of this. It's cut'n'paste from homework or a test.

Also, the stuff about the "1960s pointer chains is not relational"... that's a load of hooie.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

AXPRRF wrote:

Removing post

Lordy... I hope you got it. If you'd shown that you tried, you might have gotten some help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 5

Joe, some of the stuff you said I agree with, but some of that was a bit uncalled for don't you think? "Perhaps you should read a book?". Looking at the post, it was a homework type question and the OP was just not sure where to start given those table structures. I'm sure everyone has worked on a database they didn't set up and thought to themselves "who designed it this way?". I have a table that has a DATETIME column to store the modified date and a DATETIME column to store the modified time. 3rd party application so I can't do anything to fix that.

I am sure everyone at some point in their DBA career has come across a table or two (or possibly an entire database) that doesn't follow the ISO standards. My habit is to follow design principles of the original developer on that database rather than try to change the naming of pre-existing objects just to get it ISO compliant.

That being said, I am not saying that the ISO standards for database naming are wrong and should be avoided, just that I don't think you need to blindly follow them. Imagine if a table called "Employee" existed on the system for years and you are new to working on that system. They want to make a "Customer" table on it. Would you recommend calling the table "Customers" knowing that Employee is used in many places and renaming it would cause a lot of work to rename (stored procedure updates, trigger updates, view updates, application code updates, etc) OR would you stick with the existing naming structure of non-plural tables for consistency sake?

I am not saying that calling it by a plural name is bad, just saying that it might not be the best option based on the current database naming structure. At my workplace for example, we have in our SQL Coding standards document that table names should describe what is in a ROW inside the database, not what is inside the table. This was set in place long before I started as a DBA and changing that would have been a huge pain in the butt for little benefit. Therefore, we have left that in there.

Not trying to pick a fight or anything, just pointing out that following the ISO standards isn't always entirely possible or doesn't always make sense.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.