MySQL homeowner database
For the table definitions, just use an INT for the keys.  You probably aren't saving anything by using SMALLINTs.  Schema changes tend to be a pain in larger projects, so you might as well try to avoid any possibility of trouble.

CREATE UNIQUE INDEX index_PK ON owner_property(property_id, owner_id); is redundant, since the table already has PRIMARY KEY properties_PK(owner_id, property_id).

The foreign key constraints look well done.

Having separate tables just for email and phone_number is probably overkill.  The e-mail addresses and phone numbers could just be attributes on the owner_email and owner_number tables, respectively.  I would put NOT NULL constraints on both of those attributes.  The phone_number field should probably be longer than VARCHAR(10).  (Note that MySQL tends to silently discard the part of the string beyond the length limit.)

Similarly, VARCHAR(50) for the property address is probably too stingy.



For your first query…


select af.property_id 
from property af
inner join owner_property op
on af.property_id = op.property_id
where op.owner_id = 1; //find all properties of owner_id = 1



… you don't need to join anything at all.

select property_id 
    from owner_property op
    where owner_id = 1;




For your second query…


select a.*
from home_owner a
inner join owner_property op
on a.owner_id = op.owner_id
where op.property_id = 3; //find all owners of property_id = 3




Use better indentation.  SELECT has a FROM clause and a WHERE clause.  FROM has an INNER JOIN.  INNER JOIN has an ON.
Use a table alias that makes better sense (or don't use an alias at all).




select o.*
    from home_owner o
        inner join owner_property op
            on o.owner_id = op.owner_id
    where op.property_id = 3; //find all owners of property_id = 3