This is correct You got so much wrong in your design. I'm trying to figure out where to start. Your column declarations make no sense; a variable length. 30 characters ZIP penal_code? In what universe? These are even tables because they don't have a key. Having a table named "Person" is absurd; it's the equivalent of having a table named "Things", which would also violate the law of identity (remember your freshman logic course? To be is to be something in particular; to be nothing in particular or something in general, is to be nothing at all). Also, since you have a singular table name that means you have only one entry in the table so it is this person do? What role does he play in your logical data model? Since all of your columns can be null, there's no way you could ever have a key, and this could ever be a table!
If you get a chance to spend some time reading ISO and other standards, you will notice there is no such thing as a universal magic generic "code", and that the codes they do standardize are usually fixed length. This is so we can figure out how to put them into printed forms, display screens, and so on,
While they are very boring, you might want to actually read the ISO 11179 naming rules for data elements. You might also want to stop using the formatting that we used to have on punchcards over 50 years ago. All uppercase data element names are hard to read and lead to a high error rate (I spent a couple of years at AIRMICS looking at the research on code readability).
Let me do a quick rewrite on all of this, using International Postal Union recommendations for the length of various fields in an address label. These standards are based on a 3-1/2 inch label using a 10 characters per inch printer and they been established for several decades. I also arbitrarily decided, since we have no other clues, that you're dealing with criminals! Hey, why not? Are you starting to understand when I say things cannot be so generic?
Besides not having the key, you don't seem to understand that identifiers are not numeric and can never be numeric by definition. You've never had a course or read a book on scales and measurements, have you? There is no such thing as a generic magical physical identifier, in RDBMS.
CREATE TABLE Criminals
(inmate_nbr CHAR(18) NOT NULL PRIMARY KEY, -- required, not optional
penal_code CHAR(8) NOT NULL, -- no idea what this might be, but it's less vague than just "code"
inmate_lastname VARCHAR(35) NOT NULL, -- postal Union rules
inmate_firstname VARCHAR(35) NOT NULL,
street_address_1 VARCHAR(35) NOT NULL,
street_address_2 VARCHAR(35) NOT NULL,
city_name VARCHAR(25) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(5) NOT NULL);
As I said this a quick rewrite. I did not put in any of the constraints that you would if you were doing a real table. But at least the table is table; it has a key and the datatypes are sensible. Let's go to your second table. Did you know we have a date data type? There is no reason to use the old Sybase DATETIME data type for the last 15 or 20 years.
CREATE TABLE Criminal_History
(inmate_nbr CHAR(18) NOT NULL
REFERENCES Criminals (inmate_nbr),
event_name VARCHAR (75) NOT NULL,
PRIMARY KEY (inmate_nbr, event_name),
event_occurence_date DATE NOT NULL,
event_start_date DATE NOT NULL,
event_end_date DATE NOT NULL,
CHECK (event_occurence_date BETWEEN event_start_date AND event_end_date),
CHECK (event_start_date <= event_end_date) );
Please carefully study everything that I added to your DDL. Most important one is a references clause. This means that your two tables are part of an RDBMS schema and not unrelated, disjoint, separate files. They are related. Your SQL engine will handle a lot of the work that you would otherwise have to do by hand. After 35+ years working in SQL, I tell people that 80 to 90% of the work of an RDBMS is done in the DDL; what you have that right. The DML is trivial.
Please note the CHECK constraints. The first one puts your event between the start and end time stamps of the event. The second constraint guarantees the event interval is correctly ordered. The optimizer will use this.
I'm not trying to be nasty, but I'm also trying to write a book on why people write bad SQL. Can you give me some idea what your programming back out round was and why you thought this was the right way to code something.
You can reply to me privately.
Please post DDL and follow ANSI/ISO standards when asking for help.