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.
  • 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
7SoulsData Analyst/Database AdministratorAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Yes.  To be explicit, the key for t2 is ( userid, phone_type ) and the key for t3 is ( phone_type ).
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, EE is not a homework site, and real world situations do not begin with 'Assuming...', so please click on the article link, read the contents, and understand what your role is here.

Many experienced experts including myself do not answer obvious homework questions.
2
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
7SoulsData Analyst/Database AdministratorAuthor Commented:
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
0
 
IrogSintaCommented:
@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
0
 
Snarf0001Commented:
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.
0
 
David Johnson, CD, MVPConnect With a Mentor OwnerCommented:
A quick test site is http://sqlfiddle.com/
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Snarf0001Commented:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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,phone_number

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
0
 
Snarf0001Commented:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Snarf0001Commented:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Snarf0001Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Snarf0001Commented:
Fair enough, though I often have that use case in practice, you're right, it wasn't part of the original question.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
Your original q stated:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
Scott PletcherSenior DBACommented:
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'/'pager'*/

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.
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
7SoulsConnect With a Mentor Data Analyst/Database AdministratorAuthor Commented:
@Scott
Thanks, it's getting clearer I couldn't see what you were doing without a Third table getting built. So will this design work?

TABLE T1: userid(primary key), phone_type, name
TABLE T2: userid, phone_type smallint /*FK to table3 table*/, phone_number

TABLE T3: phone_type smallint, phone_type_description /*'home'/'work'/'cell'/'pager'*/



Thanks,
7Souls
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
@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
0
 
7SoulsData Analyst/Database AdministratorAuthor Commented:
Thanks for your help. I will look for your help in the help in the future.
0
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.

All Courses

From novice to tech pro — start learning today.