How do I get someone to check the solutions to the DBA questions below:
Hello experts, I have a few DBA questions that I need checking out, and I don't have access to a sample Oracle and SQL/Server Database.
Question 1: Assuming table t1 has the following columns:
userid (primary key), name, phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
Redesign t1, create a table t2 to join to table t1, and normalize the database by eliminating the “repeating group” of phone number columns in t1.
Ans.
Please let me know if steps are correct or if I missed something.
Question 2: Assuming a character column named [title] in table t1 with the following values:
The Day Of The Jackal
Sleepless In Seattle
Point Blank
Gone With The Wind
What is the result set of the following SQL statement?
SELECT LOWER(SUBSTR(title,1,7)) FROM t1
Ans.
Assuming that this statement is a syntactically correct MySql statement. The result set should look like the following:
Could anyone check to see if this result set is correct?
Question 3: What are the most likely physical file names for a SQL Server database named MyDB, including the standard file extensions?
I could look this up but if you know the answer or know where I can get it quickly, I'd appreciate it.
Thanks experts I look forward to your response,
7Souls
Question 1: Assuming table t1 has the following columns:
userid (primary key), name, phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
Redesign t1, create a table t2 to join to table t1, and normalize the database by eliminating the “repeating group” of phone number columns in t1.
Ans.
- The goal is to "Create table t2 and copy the contents of table t1 into table t2 and join table t1 using userid (primary key), ".
- This achieved with the use of a SQL "Create table t2 (select * from t1);"
- Then you will need to maintain referential integrigty by using a SQL "Alter table t2 adding a (t2.userid) Foreign Key from table t2 restraint that references (t1.userid) Primary Key field from table t1.
- You should will make a backup copy of table t1. Then finally you should do a SQL "Alter table t1 drop ( phone_num_home, phone_num_work, phone_num_cell, phone_num_pager)
Please let me know if steps are correct or if I missed something.
Question 2: Assuming a character column named [title] in table t1 with the following values:
The Day Of The Jackal
Sleepless In Seattle
Point Blank
Gone With The Wind
What is the result set of the following SQL statement?
SELECT LOWER(SUBSTR(title,1,7)) FROM t1
Ans.
Assuming that this statement is a syntactically correct MySql statement. The result set should look like the following:
- The Day Of The Jackal --->the day
- Sleepless In Seattle -->sleeple
- Point Blank -->point b
- Gone With The Wind -->gone wi
Could anyone check to see if this result set is correct?
Question 3: What are the most likely physical file names for a SQL Server database named MyDB, including the standard file extensions?
I could look this up but if you know the answer or know where I can get it quickly, I'd appreciate it.
Thanks experts I look forward to your response,
7Souls
As Jim commented, Experts shouldn't answer homework questions but we are happy to point you the right path so you can find your own answers. If that is good for you, let us know so we can point you what you should check to obtain the answers you need.
ASKER
Thanks experts for responding so quickly. I'm not asking for help on home work questions. But I'm asking for someone to take few minutes and just let me know if my Answers are close to correct.
I don't have the resources to build a Db or the bandwidth, otherwise the time asking for help along with the cost makes the service not worth it.
So, If you know of a site that I can build Db's on and don't have to pay for great. I've been a member of this site for over 10 years and I'm here for solutions not "bullying".
Regards,
7Souls
I don't have the resources to build a Db or the bandwidth, otherwise the time asking for help along with the cost makes the service not worth it.
So, If you know of a site that I can build Db's on and don't have to pay for great. I've been a member of this site for over 10 years and I'm here for solutions not "bullying".
Regards,
7Souls
@7Souls,
I don't believe any "bullying" was intended. Your question wasn't formatted correctly and at first glance it looked like you submitted a bunch of homework questions since your answers didn't stand out from the questions. That's why I reformatted your original post.
I'm not a SQL SERVER expert but IMO your answer to the question 2 is correct. Hopefully someone else will chime in and provide you with better guidance.
Ron
I don't believe any "bullying" was intended. Your question wasn't formatted correctly and at first glance it looked like you submitted a bunch of homework questions since your answers didn't stand out from the questions. That's why I reformatted your original post.
I'm not a SQL SERVER expert but IMO your answer to the question 2 is correct. Hopefully someone else will chime in and provide you with better guidance.
Ron
I think you should re-read the questions a bit.
As stated above, #3 I'll leave alone.
#2: is close, but google it quickly. "substr" isn't valid in MSSQL.
#1: larger one...
First, creating with "select into" syntax is also different for MSSQL.
But moreover, I think you misunderstood the question. You're basically duplicating the table, but the question seems to be asking you to normalize. So you DON'T have HomePhone, CellPhone, WorkPhone, PagerPhone, etc... and only have a single PHONE column, which links back to the first table.
From the way you described, you're just duplicating and making a 1:1 lookup table instead of the 1:many they're looking for.
As stated above, #3 I'll leave alone.
#2: is close, but google it quickly. "substr" isn't valid in MSSQL.
#1: larger one...
First, creating with "select into" syntax is also different for MSSQL.
But moreover, I think you misunderstood the question. You're basically duplicating the table, but the question seems to be asking you to normalize. So you DON'T have HomePhone, CellPhone, WorkPhone, PagerPhone, etc... and only have a single PHONE column, which links back to the first table.
From the way you described, you're just duplicating and making a 1:1 lookup table instead of the 1:many they're looking for.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Ron and @Snarf0001
I apologize for getting defensive. And thanks for reformatting my questions. Again experts I tried to answer the questions first I just need a sounding board.
@David Johnson
@Snarf0001
I think you are confusing what my answer is for question 1.) after you do a Oracle SQL (Alter drop phone number colunms on table t1) leaving only a 1:1 by userid on both tables t1 and t2.
@David Johnson CD, MVP
THANK YOU !!! for the site. I will check it out soon.
Thanks,
7Souls
I apologize for getting defensive. And thanks for reformatting my questions. Again experts I tried to answer the questions first I just need a sounding board.
@David Johnson
@Snarf0001
I think you are confusing what my answer is for question 1.) after you do a Oracle SQL (Alter drop phone number colunms on table t1) leaving only a 1:1 by userid on both tables t1 and t2.
@David Johnson CD, MVP
THANK YOU !!! for the site. I will check it out soon.
Thanks,
7Souls
Right, but that's specifically what I think they do NOT want you to do.
They're asking for table normalization. Generally that means that they are looking to not have Home, Cell and multiple repeated phone number columns, when they're all saving the same basic data "type".
Pretty sure they're after having the second table with a SINGLE phone number column (and optionally a type column to specify what kind), with a 1:many between the users table and the "UserPhoneNumbers" table.
That way if the user has no phone numbers, you have no rows. If they have 18 phone numbers, your system can handle it.
They're asking for table normalization. Generally that means that they are looking to not have Home, Cell and multiple repeated phone number columns, when they're all saving the same basic data "type".
Pretty sure they're after having the second table with a SINGLE phone number column (and optionally a type column to specify what kind), with a 1:many between the users table and the "UserPhoneNumbers" table.
That way if the user has no phone numbers, you have no rows. If they have 18 phone numbers, your system can handle it.
ASKER
@Snarf0001
Thanks for the comment I forgot about the repeating group that I created in table t2. The problem that I'm having is visualizing this.
With normalization are you stating:
Table t1 - userid(pk),name,phone_type
Table t2 - userid_fk(fk),phone_type,p
or Are you suggesting something else.
Thank you this is helpful, I'll keep my comments shorts I'm working on a Kindle Fire.
7Souls
Thanks for the comment I forgot about the repeating group that I created in table t2. The problem that I'm having is visualizing this.
With normalization are you stating:
Table t1 - userid(pk),name,phone_type
Table t2 - userid_fk(fk),phone_type,p
or Are you suggesting something else.
Thank you this is helpful, I'll keep my comments shorts I'm working on a Kindle Fire.
7Souls
Hey, no problem.
And as you have it there now is basically what I think they're asking for.
There's no point in the "phone_type" in T1though, and of course you'll need some kind of ID / PK for T2.
And as you have it there now is basically what I think they're asking for.
There's no point in the "phone_type" in T1though, and of course you'll need some kind of ID / PK for T2.
ASKER
@Snarf0001,
Yeah, I let the description of the design of the problem get in the way of the solution. Instead of a vertical data consumption on table t2 your addition of the phone_type on table t2 allowed for more of a space saving horizontal table.
So no Foreign key on table t2. Just a constraint between two PK's from table t1 and t2.
Thanks again and let me know if I got it,
7Souls
Yeah, I let the description of the design of the problem get in the way of the solution. Instead of a vertical data consumption on table t2 your addition of the phone_type on table t2 allowed for more of a space saving horizontal table.
So no Foreign key on table t2. Just a constraint between two PK's from table t1 and t2.
Thanks again and let me know if I got it,
7Souls
The way I read the question, along with the suggestion and your last design were all targeting a vertical table, not horizontal.
As I understood, what YOU were originally planning was:
T1: userid, name
T2: userid, phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
And what I think they're after, and what I suggested was:
T1: userid, name
T2: phoneid (pk), userid (fk), phonetype, phone
So T2 WOULD have an FK to the users table to make the link.
And if "John Smith" had 10 different phone numbers, he would have 10 rows with his UserID in T2.
As I understood, what YOU were originally planning was:
T1: userid, name
T2: userid, phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
And what I think they're after, and what I suggested was:
T1: userid, name
T2: phoneid (pk), userid (fk), phonetype, phone
So T2 WOULD have an FK to the users table to make the link.
And if "John Smith" had 10 different phone numbers, he would have 10 rows with his UserID in T2.
ASKER
@Snarf0001
That's it! Question why the addition of a 'phoneid(pk)' on T2 ?
So you agree with with the JOIN at T2 userid(FK) and T1 userid(PK).
I'm fine with this thanks,
7Souls
That's it! Question why the addition of a 'phoneid(pk)' on T2 ?
So you agree with with the JOIN at T2 userid(FK) and T1 userid(PK).
I'm fine with this thanks,
7Souls
If you don' t know what normalization is, you need to look that up and study it. It is a critical requirement for a DBA (except for some very large IT shops that have physical-only DBAs, but most shops don't).
The key to table2 should be:
( userid, phone_type )
where phone_type is a code that represents the phone type, i.e., 1 = home, 2 = work, etc.. There will be a separate table to look up the phone type description -- that is part of normalization as well.
Also, note that table2 does not require an ID column at this point, and thus should not have one.
Rule: Every table does NOT need an ID column.
Repeat that to yourself 50 times so you believe it, even if almost all developers don't.
The key to table2 should be:
( userid, phone_type )
where phone_type is a code that represents the phone type, i.e., 1 = home, 2 = work, etc.. There will be a separate table to look up the phone type description -- that is part of normalization as well.
Also, note that table2 does not require an ID column at this point, and thus should not have one.
Rule: Every table does NOT need an ID column.
Repeat that to yourself 50 times so you believe it, even if almost all developers don't.
I'm actually going to second-guess Scott here.
Though the "DOES NOT NEED AN ID COLUMN" is definitely true, in this case I think it should, and the PK should not be based on type.
Myself and a few people I know for example have two different cell numbers, and could have two or more different work numbers.
In which case you'll have multiple records with the same UserID / PhoneType.
Though the "DOES NOT NEED AN ID COLUMN" is definitely true, in this case I think it should, and the PK should not be based on type.
Myself and a few people I know for example have two different cell numbers, and could have two or more different work numbers.
In which case you'll have multiple records with the same UserID / PhoneType.
That is theoretically possible. But the original table did NOT allow for that. Since you've only been asked to normalize an existing table, I wouldn't jump ahead of that.
If you did want to allow multiples, you could add an id, a sequence#, or simply allow duplicate types.
If you did want to allow multiples, you could add an id, a sequence#, or simply allow duplicate types.
Fair enough, though I often have that use case in practice, you're right, it wasn't part of the original question.
and the PK should not be based on type.
Sorry, I wasn't as clear as I needed to be there.
My primary concern is performance. Most critical to that is how the table is clustered. I simply wrote "key" when I should have written "clustering key". I frankly don't care if the table has a PK or not, although I know that is theoretically required of every table.
Although I personally would never use an ID alone as the PK for this table, you could do that as long as it was a nonclustered PK, and the clustering key was still ( userid, type, id ). Clustering by ID alone would cause lots of extra I/O and CPU use when joining to the table.
ASKER
@Scott and @Snarf0001
Thank you, I do appreciate the discussion on Normalization. But as I go back over the original question it only asked for the "repeating group of phone numbers" to be eliminated on TABLE T1 and a join TABLE T2.
I know it sounds like I'm going back but maybe I should "Denormalized" where we are just for the sake of this exercise.
I probably can present both examples, let me know what you think.
Thanks,
7Souls
Thank you, I do appreciate the discussion on Normalization. But as I go back over the original question it only asked for the "repeating group of phone numbers" to be eliminated on TABLE T1 and a join TABLE T2.
I know it sounds like I'm going back but maybe I should "Denormalized" where we are just for the sake of this exercise.
I probably can present both examples, let me know what you think.
Thanks,
7Souls
ASKER
@David Johnson
Thanks I used your http://sqlfiddle.com. I was able to build an object and test the SQL on this site on this over worked Kindle fire. I hope to do some Business with you @Snarf0001 and Scott in the future.
Thanks,
7Souls
Thanks I used your http://sqlfiddle.com. I was able to build an object and test the SQL on this site on this over worked Kindle fire. I hope to do some Business with you @Snarf0001 and Scott in the future.
Thanks,
7Souls
Your original q stated:
I can't imagine you added the "normalize" part just for here. I strongly suspect that was on the original q as well.
Again, if you don't understand normalization, you need to learn about it, especially as a DBA. It's hard to conceive of a "DBA" that doesn't know anything about normalization.
and normalize the database by eliminating the “repeating group” of phone number columns in t1.
I can't imagine you added the "normalize" part just for here. I strongly suspect that was on the original q as well.
Again, if you don't understand normalization, you need to learn about it, especially as a DBA. It's hard to conceive of a "DBA" that doesn't know anything about normalization.
ASKER
@Scott
I thought I had a handle on this question. I don't do much design work however I've read about the practice and had done Oracle class work in the past.
I'm trying to just focus on what this question is asking me to do.
Again with the design I started with did it fit the requirement of the question?
Thanks,
7Souls
I thought I had a handle on this question. I don't do much design work however I've read about the practice and had done Oracle class work in the past.
I'm trying to just focus on what this question is asking me to do.
Again with the design I started with did it fit the requirement of the question?
Thanks,
7Souls
ASKER
@Experts
So what is the right answer to the TABLE design question?
So I still need help. Or did I offer you guys a workable solution based on what the question asked for.
Or did the solution that @Snarf0001 offered better?
@Scott, It's good that you have a deep understanding of Normalization. However I don't think the question is asking for 4 degree normal form, or I might be wrong. However I still could use your help with this.
Thanks again,
7Souls
So what is the right answer to the TABLE design question?
So I still need help. Or did I offer you guys a workable solution based on what the question asked for.
Or did the solution that @Snarf0001 offered better?
@Scott, It's good that you have a deep understanding of Normalization. However I don't think the question is asking for 4 degree normal form, or I might be wrong. However I still could use your help with this.
Thanks again,
7Souls
Is this a DBA q or a developer q?
If it's a DBA q, then I would expect 3NF (nothing above involves 4NF). And, again, anyone calling himself/herself a "DBA" should have a decent enough understanding of normalization to do that simple a table design, something along the lines of:
table2 userid, contact_type FK to contact_types, contact_value; clus on ( userid, contact_type )
contact_types contact_type smallint (PK), contact_type_desc varchar(50)
If it's a developer q, I'd be thrilled if they could come up with anything reasonable that did not include an id -- probably a forlorn hope.
If it's a DBA q, then I would expect 3NF (nothing above involves 4NF). And, again, anyone calling himself/herself a "DBA" should have a decent enough understanding of normalization to do that simple a table design, something along the lines of:
table2 userid, contact_type FK to contact_types, contact_value; clus on ( userid, contact_type )
contact_types contact_type smallint (PK), contact_type_desc varchar(50)
If it's a developer q, I'd be thrilled if they could come up with anything reasonable that did not include an id -- probably a forlorn hope.
ASKER
@Scott
Your help is appreciated. And to answer your question. I am a premium subscriber. So shouldn't we just work on solutions since both our life times are both short.
Thanks, for your help.
So your previous post discuss 'Third Normal Form' now it's slowly coming on into view for me can you help me with the table t2 design.
TABLE T1: userid, contact_type, name
TABLE T2: userid, contact_type FK to contact_types, contact_value; clustered on ( userid, contact_type ),
contact_types contact_type smallint (PK), contact_type_desc varchar(50)
Thanks,
7Souls
Your help is appreciated. And to answer your question. I am a premium subscriber. So shouldn't we just work on solutions since both our life times are both short.
Thanks, for your help.
So your previous post discuss 'Third Normal Form' now it's slowly coming on into view for me can you help me with the table t2 design.
TABLE T1: userid, contact_type, name
TABLE T2: userid, contact_type FK to contact_types, contact_value; clustered on ( userid, contact_type ),
contact_types contact_type smallint (PK), contact_type_desc varchar(50)
Thanks,
7Souls
Within the context of that q, these are the table2, and the needed table3, designs:
table2: userid, contact_type smallint /*FK to table3 table*/, phone_number
table3: contact_type smallint, contact_type_description /*'home'/'work'/'cell'/'pa
Table3 is needed for standard normalization reasons -- it reduces update complexity and potential for errors. For example, should the company change all 'pager' types to 'Skype', a single row change is all that is required, not every phone row for every person that has a pager
If I asked that q, of a potential DBA, I personally would be looking for someone who knew that a third table was needed to satisfy normalization and was willing to state that.
table2: userid, contact_type smallint /*FK to table3 table*/, phone_number
table3: contact_type smallint, contact_type_description /*'home'/'work'/'cell'/'pa
Table3 is needed for standard normalization reasons -- it reduces update complexity and potential for errors. For example, should the company change all 'pager' types to 'Skype', a single row change is all that is required, not every phone row for every person that has a pager
If I asked that q, of a potential DBA, I personally would be looking for someone who knew that a third table was needed to satisfy normalization and was willing to state that.
ASKER
@Scott
Thanks, for the clarity. Let say I only have two tables t1 and t2 should they get normalized at the 1NF?
TABLE T1: userid(primary key), name
TABLE T2: userid(foreign key), phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
Thanks,
7Souls
Thanks, for the clarity. Let say I only have two tables t1 and t2 should they get normalized at the 1NF?
TABLE T1: userid(primary key), name
TABLE T2: userid(foreign key), phone_num_home, phone_num_work, phone_num_cell, phone_num_pager
Thanks,
7Souls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Scott and @David Johnson and @Ron, thanks for your help. I will you use your solutions when I'm presented with the same issue.
I will close out this question and assign points. Also, I may need help with assigning points.
Thank you,
7Souls
I will close out this question and assign points. Also, I may need help with assigning points.
Thank you,
7Souls
ASKER
Thanks for your help. I will look for your help in the help in the future.
Many experienced experts including myself do not answer obvious homework questions.