0
\$\begingroup\$

We are to normalize a database, though it is mostly all theoretical, we aren't actually plugging in the SQL. This is the Data we have normalized to these tables..

enter image description here

and here is our SQL for normalizing it. I'd just like to know if anyone spots any glaring issues.

insert into bldg_table (bldg)
Select distinct bldg from apartmentmaster order by bldg;

----------------------------------------------------------

insert into id_rec (id, firstName, lastName)
Select id, tenantfirstname, tenantlastname from apartmentmaster where apt not in (603, 602);

----------------------------------------------------------

insert into apt_bldg_table(aptNumber, baseRent, bldg)
select apartmentmaster.apt, apartmentmaster.baseRent, apartmentmaster.bldg
from apartmentmaster;

---------------------------------------------------------

insert into tenant_apt_table (id, aptbldg, begdate, baseRent)
select apartmentmaster.id, aptbldg_table.rec_no, "2015-01-01", aptbldg_table.baseRent
from apartmentmaster
join apt_table
on apartmentmaster.apt = aptbldg_table.aptNumber and
apartmentmaster.bldg = aptbldg_table.bldg;

----------------------------------------------------------

insert into rent_rec (tenant_apt_id, paid_date, paid_amt)
select
    tenant_apt_table.rec_no, apartmentmaster.janPaid, apartmentmaster.janRent
from apartmentmaster
join apt_bldg_table
on apartmentmaster.Apt = apt_bldg_table.aptNumber and apartmentmaster.Bldg = apt_bldg_table.bldg
join
    tenant_apt_table
on tenant_apt_table.aptBldg = apt_bldg_table.rec_no

/*and we repeat this query five more times for February, March, etc.*
----------------------------------------------------------
\$\endgroup\$
2
  • 5
    \$\begingroup\$ "Does it have any major faults?" Does it work as intended? \$\endgroup\$ – πάντα ῥεῖ Apr 18 '17 at 22:32
  • 4
    \$\begingroup\$ We can review the SQL code, but with this kind of question, unless you show tue table creation code, what we can comment on is pretty limited. \$\endgroup\$ – Phrancis Apr 19 '17 at 4:59
1
\$\begingroup\$

I have added some comments to your code since, as comments have stated, the relational design is really hard to comment on since we don't know all the business requirements.

There is no need to have an order by with an insert statement since when querying, the order isn't deterministic unless you specify an ORDER BY. Thus, attempting to insert data in an order isn't really necessary

insert into bldg_table (bldg)
Select distinct bldg from apartmentmaster order by bldg;

----------------------------------------------------------

insert into id_rec (id, firstName, lastName)
Select id, tenantfirstname, tenantlastname 
from apartmentmaster where apt not in (603, 602);

----------------------------------------------------------

In the first two queries you didn't fully qualify your column names as there was no reason to. In this segment, and the following ones, you used a full qualifier of table.column. Since you are selecting from a single table, this is unnecessary. If you do want to fully qualify it, I would use table aliases. See below for the change.

insert into apt_bldg_table(aptNumber, baseRent, bldg)
select aptmstr.apt, aptmstr.baseRent, aptmstr.bldg
from apartmentmaster as aptmstr;

---------------------------------------------------------

Instead of repeating this for Feburary, March, etc as you stated below, you could create a dates table variable and use CROSS JOIN to eliminate the need to run this five times. See this link for a quick demo. Also, if you want to hard code the date as you have done, and want to use double quotes you need to SET QUOTED_IDENTIFIER OFF.

insert into tenant_apt_table (id, aptbldg, begdate, baseRent)
select apartmentmaster.id, aptbldg_table.rec_no, "2015-01-01", aptbldg_table.baseRent
from apartmentmaster
join apt_table
on apartmentmaster.apt = aptbldg_table.aptNumber and
apartmentmaster.bldg = aptbldg_table.bldg;

----------------------------------------------------------

insert into rent_rec (tenant_apt_id, paid_date, paid_amt)
select
    tenant_apt_table.rec_no, apartmentmaster.janPaid, apartmentmaster.janRent
from apartmentmaster
join apt_bldg_table
on apartmentmaster.Apt = apt_bldg_table.aptNumber and apartmentmaster.Bldg = apt_bldg_table.bldg
join
    tenant_apt_table
on tenant_apt_table.aptBldg = apt_bldg_table.rec_no

/*and we repeat this query five more times for February, March, etc.*
----------------------------------------------------------
\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.