Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Prove that R ⟕ S = R ⨝ S

R
Raziyah00 last edited by
Given the relations R(a, b, c) and S(b, d), where {R.a, R.b} is the primary key of R, {S.b} is the primary key of S, and {R.b} is the foreign key in R, which refers to {S.b} (the data is already give as sample on the website)

Running following equations
- R ⟕ S
- R ⨝ S
in https://dbis-uibk.github.io/relax/calc/local/uibk/local/0 gives the same result for both but how can it be proved like mathematically?

R ⟕ S = R ⨝ S
Reply Quote 0
1 Reply Last reply
M
meriah last edited by

Since {R.b} is the foreign key referencing S, each tuple in R is guaranteed to have one and only one matching tuple in S, therefore the natural join will result in all R tuples being selected exactly once.
Reply Quote 0
1 Reply Last reply

relational algebra 36

2
Posts

1
Views

Suggested Topics

Understanding Query Plan’s “width”
SQL, Database Testing • postgresql • • jeanid

2

0
Votes

2
Posts

0
Views

N

Every PostgreSQL row version (“tuple”) has a number of system columns and other data that are not displayed by SELECT * (see the documentation for details. Among them are xmin and xmax extra flags that contain the “hint bits” and the “frozen” flag. ctid, the tuple's physical address. These would account 23 bytes, not for four, so you'd have to look at tge source for more. The only of these items that occupies 6 bytes is ctid, so maybe that.
generate alert if someone trigger ad-hoc backup and it's not copy_only backup
SQL, Database Testing • sql server backup • • Demir

2

0
Votes

2
Posts

1
Views

T

First you want to retrieve the normal information from your backup history on the relevant SQL Server instance, to find out what you normal backup file location looks like. You can achieve this with something like this: SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CASE msdb..backupset.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS backup_type, -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB], CASE WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk' WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape' WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device' ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!' END AS device_type_desc, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description, msdb.dbo.backupset.is_copy_only, msdb.dbo.backupset.is_snapshot, msdb.dbo.backupmediaset.name, msdb.dbo.backupmediaset.software_name, msdb.dbo.backupset.user_name, 'EOR' FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id INNER JOIN msdb.dbo.backupmediaset ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id WHERE 1 = 1 ORDER BY msdb.dbo.backupset.backup_start_date DESC; A result set for when using Commvault as your backup solution could look like this: database_name backup_start_date backup_type device_type_desc physical_device_name logical_device_name backupset_name description is_copy_only is_snapshot name software_name user_name TVDTools 2021-10-25 08:04:14.000 Log Virtual Device 8e5bda3c-d453-4328-b180-dcee74549f68 NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER TVDTools 2021-10-24 21:01:58.000 Log Virtual Device f40f904f-e197-4c2b-88fe-b9cdb5ee237f NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER master 2021-10-24 20:16:02.000 Full Virtual Device 361ff522-8e39-406c-b971-5808ee51240b NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER TVDTools 2021-10-24 20:06:44.000 Diff Virtual Device 3589d70e-af8d-469f-ad66-5e1ca52cef1e NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER msdb 2021-10-24 20:06:44.000 Diff Virtual Device 12685e62-f688-4818-96d3-e20a802af6b9 NULL CommVault Galaxy Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN\USER So you have to determine with your backup solution which columns tell you when something is being run outside you backup solution. In my case I'd just search for any backup set that didn't have CommVault Galaxy% in the backupset_name column and possibly which weren't using a device_type code of 7. Your mileage may vary. Answering Your Questions Combining the above query with a couple of WHERE clauses would result in the following query, which you could possibly plug into your monitoring tool: SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, CASE msdb..backupset.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS backup_type, -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB], CASE WHEN msdb.dbo.backupmediafamily.device_type = 2 THEN 'Disk' WHEN msdb.dbo.backupmediafamily.device_type = 5 THEN 'Tape' WHEN msdb.dbo.backupmediafamily.device_type = 7 THEN 'Virtual Device' WHEN msdb.dbo.backupmediafamily.device_type = 9 THEN 'Azure Storage' WHEN msdb.dbo.backupmediafamily.device_type = 105 THEN 'A permanent backup device' ELSE 'Unknown backup device code:' + CAST(msdb.dbo.backupmediafamily.device_type AS VARCHAR(3)) + '!' END AS device_type_desc, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description, msdb.dbo.backupset.is_copy_only, msdb.dbo.backupset.is_snapshot, msdb.dbo.backupmediaset.name, msdb.dbo.backupmediaset.software_name, msdb.dbo.backupset.user_name, 'EOR' FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id INNER JOIN msdb.dbo.backupmediaset ON msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id WHERE 1 = 1 -- Is Not CommVault Backup Check AND (msdb.dbo.backupmediafamily.device_type <> 7 AND msdb.dbo.backupset.name NOT LIKE 'CommVault Galaxy%') -- Last Hour Check AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102)) >= DATEADD(hh,-1,GETDATE()) -- Is Not Copy_Only Check AND msdb.dbo.backupset.is_copy_only <> 1 ORDER BY msdb.dbo.backupset.backup_start_date DESC; Which results in the following output: database_name backup_start_date backup_type device_type_desc physical_device_name logical_device_name backupset_name description is_copy_only is_snapshot name software_name user_name (No column name) TVDTools 2021-10-25 09:21:07.000 Full Disk H:\adhoc\SERVERNAME\TVDTools_Full_CopyOnly_20211025_092107.bak NULL TVDTools-Full Backup NULL 0 0 NULL Microsoft SQL Server DOMAIN/User EOR Gotcha! The results can be used in your monitoring software to do whatever you want to do, e.g. send an e-mail with the results, trigger an alarm in the console, ...
A

Minimum and Maximum Number of Tuples
SQL, Database Testing • relational algebra • • Amritpal

2

0
Votes

2
Posts

0
Views

Yes, your answers are mostly right, except a few mistakes: 1. R UNION S max : n+m ( union we add all the tuples from both relations) Correct, when R and S have no common tuple. min: 0 ( taking m=n=null ) Wrong, the minimum is n (the greatest of the two sizes, m and n). When all the tuples of R also exist in S. And m and n cannot be null, they are the sizes of the relations, they are numbers (integers). 2. R INTERSECTION S max : m ( m both relation contains same keys then we may get maximum m keys ) Correct but the reasoning is wrong. You compare tuples of the two relations, not keys. min: 0 ( taking m=n=null if no common keys in both relations) Correct but the reasoning is wrong. The result can be 0 because the two relations may have no common tuples). 3. R - S max : m ( if they are disjoint then in R-S we will get all tuples of R ) Correct min: 0 ( if all tuples in R is also present in S) Correct 4. S - R max : n ( as explained above ) Correct min: 0 ( as explained above ) Wrong, the minimum is n - m. 5. R natural join S max : n*m ( if no matching key constraints natural join will produce Cartesian product ) Correct min: m ( m < n when key constraints are taken into consideration ) Wrong, the minimum is 0. You can easily find an example, identical with case 2 (INTERSECTION). 6. R LEFT OUTER JOIN S max : m ( everything from left table will be output even if no match) Wrong, the maximum is m * n, the same as for natural join. Or just take ON TRUE. min: 0 ( when m=0 ) Wrong, the minimum is m. Example can be the same as for NATURAL join above (or just take ON FALSE) but it cannot give as a result lees than the number of tuples in R (the left relation in the join). 7. R / S max : m ( when n=0 ) Correct but it doesn't have to be n=0 or m=0. You can find another example. min: I'm not able to make a conclusion. Minimum is 0 Consider that relational division is similar to integer division. 3 / 7 gives 0 in integer division for example. Try to convert this into relational division.
S

Nontrivial joining a relation with itself
SQL, Database Testing • join self join relational algebra • • shizuka

2

0
Votes

2
Posts

0
Views

S

What you describe is simply a projection of R that includes a twice. There is no need for "renaming and natural join operations on R".
T

Should database files be on different disk(s) than OS?
SQL, Database Testing • sql server 2008 sql server 2005 database rec languageomme datafile • • terrea

2

0
Votes

2
Posts

0
Views

A

It's typically good practice to seperate your OS installation from your database installation to isolate OS disk issues from database disk issues. The primary reason for this is to reduce the change that a problem with the OS could prevent recovery of the database, or vice versa. If your database fills up the free disk space, it can crash both the database and the OS. This also allows you to have different recovery options available for the different pieces. We typically run RAID 1 (Mirroring) on our OS disks, and RAID 5 (or some other type of parity + striping) on our Database disks. Since the OS disk has fewer writes, we can take the performance hit of mirroring, to reduce recovery time in a crash situation. But on the Database side we want to maximize the performance of reads/writes at the expense of recovery time in a crash situation.
J

Postgres Database flagged as STORAGE FULL whereas there's 45 Gb available
SQL, Database Testing • postgresql aws storage amazon rds postgresql 9.6 • • jonetta

2

0
Votes

2
Posts

1
Views

K

Got an answer back from AWS support. It appears the problem came from not having enough space left for inodes. We cannot control allocated space for inodes on RDS so the only solution here is to get a bigger instance. The OOM Killer allowed to get our instance running again.
Y

Consult Databases(Relational Algebra)
Software Programming • database division relational algebra • • yajahira

2

0
Votes

2
Posts

0
Views

R

The division would be used for teachers who have done all the courses, for no course is as simple as checking that they are not in the table CURSOS REALIZADOSdirectly with an operator NOT IN: In MySQL it would be like this:SELECT Nombre FROM ( SELECT * FROM persona WHERE Tipo='D' ) p WHERE Ced NOT IN ( SELECT Doc_Est FROM cursos_realizados ) OR Ced IN ( SELECT Doc_Est FROM cursos_realizados WHERE Sede=101 );
SQL Server Sch-S locks on unrelated tables
SQL, Database Testing • sql server loc languageking bloc languageking • • Laycee

2

0
Votes

2
Posts

0
Views

J

Potential Causes There are two likely reasons for this There's a foreign key on the table There's a trigger on the table There are less likely reasons, too Computed column based on a scalar function Indexed view maintenance There's also the possibility that the blocking is due to metadata changing, where modifications to system views can block each other. Figuring It Out You can use sp_WhoIsActive. If you run it like so: sp_WhoIsActive @get_locks = 1;, it should help you get to the root of your blocking issue.
Conversion of statements to E-R Diagram
SQL, Database Testing • database design • • Marcee

2

0
Votes

2
Posts

0
Views

A

is Mentor of is a relationship between a teacher and a student. From your diagram I conclude that a course can be only be enroled by one student. I is not explicitely stated but I think that a course can be enroled by many students. Also I think that a course can be part of more then one program. Finally I get the following diagram: The diagram is created with dia and the source can be found here. In the diagram all relationships are defined mandatatory. So the the diagram should be refined in respect to this. For example it seems to be wrong that each teacher is mentor of at least one student.
S

What are pros and cons of Ola’s scripts vs using maintenance plan?
SQL, Database Testing • sql server ola hallengren • • Saumya

2

0
Votes

2
Posts

0
Views

N

I have written here Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient. To add more, Ola's maintenance solution is widely accepted in the community and large organizations. Its open sourced and issues can be raised at github/issues with a likelyhood of getting it fixed very fast. Its flexible and scalable (even if you want to deploy it to 100s of servers just use Install-DbaMaintenanceSolution from dbatools.) Microsoft took almost a decade to fix Maintenance plan GUI which was buggy itself has extensive documentation and FAQs and is constantly updated to accomodate newer sql server versions. in preview version, you can even run backups in parallel. for index maintenance solution, you can even time the process e.g. if it runs more than X amount of time, abort it.
E

Delete duplicate keys with relation algebra
SQL, Database Testing • relational theory relational algebra relational c languagealc • • emran

2

0
Votes

2
Posts

0
Views

I would do something like the following (see the fiddle here Create and populate a table: CREATE TABLE test ( key INTEGER NOT NULL, value INTEGER NOT NULL ); INSERT INTO test VALUES (1, 5), (2, 6), (2, 7), (2, 8), (4, 9); and then: SELECT * FROM test; Result: key value 1 5 2 6 2 7 2 8 4 9 and then run the following SQL: SELECT key, value FROM test WHERE key IN ( SELECT key FROM test GROUP BY key HAVING COUNT(key) = 1 ORDER BY key ); Result: key value 1 5 4 9 Which is the desired result. I have used PostgreSQL - the Open Source database which I would recommend that you use to learn relational database principles - it has a very high level of standards compliance and is a great learning tool. The inner loop of SQL SELECT key FROM test GROUP BY key HAVING COUNT(key) = 1 ORDER BY key produces those keys which have a COUNT of only 1 (in this case, the integers 1 and 4) and then the outer loop selects the values corresponding to those two integers (see the fiddle).
C

How to reuse a relational algebra expression without using assignment operator?
SQL, Database Testing • relational algebra • • charlenef

2

0
Votes

2
Posts

0
Views

C

Why can't you use? R2 = σθ(R1) and then R2 × R2
T

Theta join explanation
SQL, Database Testing • relational algebra • • tahishae

2

0
Votes

2
Posts

0
Views

O

I think you need to think some more 2) "there's no common attributes" is certainly true but irrelevant. We know there is a correspondence between C and D (and therefore are of the same type) because of the referential integrity constraint even though they have difference names. e.g. here's an example in SQL that satisfies the conditions yet returns a row: CREATE TABLE s ( D INTEGER NOT NULL, E INTEGER NOT NULL, F INTEGER NOT NULL, PRIMARY KEY (D) ); CREATE TABLE r ( A INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER NOT NULL, PRIMARY KEY (A), FOREIGN KEY (C) REFERENCES s (D) ); INSERT INTO s (D, E, F) VALUES (1, 1, 1); INSERT INTO r (A, B, C) VALUES (1, 1, 1); INSERT INTO r (A, B, C) VALUES (2, 2, 1); SELECT * FROM s JOIN r ON B = E; p.s. where you used the term 'records' you should actually use the term 'tuples'.
S

How to represent NULL in relational algebra?
SQL, Database Testing • null relational theory relational algebra projec languagetion • • Saumya

2

0
Votes

2
Posts

0
Views

There are no NULL values in relational algebra. Here you can use the antijoin operator ▷: πROLLNO, NAME(STUDENT ▷ENROLLMENT) that selects all records from STUDENT where the ROLLNO is not in ENROLLMENT. In SQL there are NULL values. You may use a left outer join and select the rows where the courseid is NULL: select STUDENT.ROLLNO, STUDENT.NAME from STUDENT left outer join ENROLLMENT on(STUDENT.ROLLNO=ENROLLMENT.ROLLNO) where ENROLLMENT.COURSEID is null
D

Operand should contain 1 column(s)
SQL, Database Testing • mysql • • derk

2

0
Votes

2
Posts

0
Views

J

The error is due to the last two COASLESCE() function in your query: For an example, you are expecting 3 values from the below COALESCE() function, but it doesn't COALESCE(( SELECT SUM(`r5`.`acctinputoctets` * (r5.percent_q / 100)), SUM(`r6`.`acctoutputoctets` * (r6.percent_q / 100)), SUM(`r6`.`acctsessiontime`) FROM `radacct` AS `r5` WHERE DATE_FORMAT(STR_TO_DATE(`r5`.`acctstarttime`, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d') AND r5.username = '1' ), ( SELECT 0, 0, 0 )), You need to change it to individual select value, then it will work.

Prove that R ⟕ S = R ⨝ S

Suggested Topics

Understanding Query Plan’s “width” SQL, Database Testing • postgresql • • jeanid

generate alert if someone trigger ad-hoc backup and it's not copy_only backup SQL, Database Testing • sql server backup • • Demir

Minimum and Maximum Number of Tuples SQL, Database Testing • relational algebra • • Amritpal

Nontrivial joining a relation with itself SQL, Database Testing • join self join relational algebra • • shizuka

Should database files be on different disk(s) than OS? SQL, Database Testing • sql server 2008 sql server 2005 database rec languageomme datafile • • terrea

Postgres Database flagged as STORAGE FULL whereas there's 45 Gb available SQL, Database Testing • postgresql aws storage amazon rds postgresql 9.6 • • jonetta

Consult Databases(Relational Algebra) Software Programming • database division relational algebra • • yajahira

SQL Server Sch-S locks on unrelated tables SQL, Database Testing • sql server loc languageking bloc languageking • • Laycee

Conversion of statements to E-R Diagram SQL, Database Testing • database design • • Marcee

What are pros and cons of Ola’s scripts vs using maintenance plan? SQL, Database Testing • sql server ola hallengren • • Saumya

Delete duplicate keys with relation algebra SQL, Database Testing • relational theory relational algebra relational c languagealc • • emran

How to reuse a relational algebra expression without using assignment operator? SQL, Database Testing • relational algebra • • charlenef

Theta join explanation SQL, Database Testing • relational algebra • • tahishae

How to represent NULL in relational algebra? SQL, Database Testing • null relational theory relational algebra projec languagetion • • Saumya

Operand should contain 1 column(s) SQL, Database Testing • mysql • • derk

Understanding Query Plan’s “width”
SQL, Database Testing • postgresql • • jeanid

generate alert if someone trigger ad-hoc backup and it's not copy_only backup
SQL, Database Testing • sql server backup • • Demir

Minimum and Maximum Number of Tuples
SQL, Database Testing • relational algebra • • Amritpal

Nontrivial joining a relation with itself
SQL, Database Testing • join self join relational algebra • • shizuka

Should database files be on different disk(s) than OS?
SQL, Database Testing • sql server 2008 sql server 2005 database rec languageomme datafile • • terrea

Postgres Database flagged as STORAGE FULL whereas there's 45 Gb available
SQL, Database Testing • postgresql aws storage amazon rds postgresql 9.6 • • jonetta

Consult Databases(Relational Algebra)
Software Programming • database division relational algebra • • yajahira

SQL Server Sch-S locks on unrelated tables
SQL, Database Testing • sql server loc languageking bloc languageking • • Laycee

Conversion of statements to E-R Diagram
SQL, Database Testing • database design • • Marcee

What are pros and cons of Ola’s scripts vs using maintenance plan?
SQL, Database Testing • sql server ola hallengren • • Saumya

Delete duplicate keys with relation algebra
SQL, Database Testing • relational theory relational algebra relational c languagealc • • emran

How to reuse a relational algebra expression without using assignment operator?
SQL, Database Testing • relational algebra • • charlenef

Theta join explanation
SQL, Database Testing • relational algebra • • tahishae

How to represent NULL in relational algebra?
SQL, Database Testing • null relational theory relational algebra projec languagetion • • Saumya

Operand should contain 1 column(s)
SQL, Database Testing • mysql • • derk